
!pip install pandasql

Requirement already satisfied: pandasql in c:\users\kayan\anaconda3\lib\site-packages (0.7.3)
Requirement already satisfied: pandas in c:\users\kayan\anaconda3\lib\site-packages (from pandasql) (1.4.3)
Requirement already satisfied: numpy in c:\users\kayan\anaconda3\lib\site-packages (from pandasql) (1.21.5)
Requirement already satisfied: sqlalchemy in c:\users\kayan\anaconda3\lib\site-packages (from pandasql) (1.4.39)
Requirement already satisfied: python-dateutil>=2.8.1 in c:\users\kayan\anaconda3\lib\site-packages (from pandas->pandasql) (2.8.2)
Requirement already satisfied: pytz>=2020.1 in c:\users\kayan\anaconda3\lib\site-packages (from pandas->pandasql) (2022.1)
Requirement already satisfied: greenlet!=0.4.17 in c:\users\kayan\anaconda3\lib\site-packages (from sqlalchemy->pandasql) (1.1.1)
Requirement already satisfied: six>=1.5 in c:\users\kayan\anaconda3\lib\site-packages (from python-dateutil>=2.8.1->pandas->pandasql) (1.16.0)


import pandas as pd 
import numpy as np
import dash as ds
import plotly.graph_objects as go
import plotly.express as px
from jupyter_dash import JupyterDash
import ast
import pprint
import timeit
from functools import reduce 
from matplotlib import pyplot as plt 
from sklearn.linear_model import LinearRegression
import statsmodels.api as sm

#set up the SQL environment
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())


# set pandas display optio
pd.set_option('display.max_colwidth', None)
pd.set_option('display.max_rows', 100)


olympics_games = pd.read_csv(r'C:\Users\kayan\Learning\Coursera - Final Project\sportstats\Milestone1\Data\olympicsgames_joined.csv')


olympics_games.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 271116 entries, 0 to 271115
Data columns (total 18 columns):
 #   Column   Non-Null Count   Dtype  
---  ------   --------------   -----  
 0   ID       271116 non-null  int64  
 1   Name     271116 non-null  object 
 2   Sex      271116 non-null  object 
 3   Age      261642 non-null  float64
 4   Height   210945 non-null  float64
 5   Weight   207183 non-null  float64
 6   Team     271116 non-null  object 
 7   NOC      271116 non-null  object 
 8   Region   271102 non-null  object 
 9   Games    271116 non-null  object 
 10  Year     271116 non-null  int64  
 11  Season   271116 non-null  object 
 12  Sport    271116 non-null  object 
 13  Event    271116 non-null  object 
 14  City     271116 non-null  object 
 15  Country  271116 non-null  object 
 16  Medal    39783 non-null   object 
 17  Notes    5039 non-null    object 
dtypes: float64(3), int64(2), object(13)
memory usage: 37.2+ MB


# fix column data types 
olympics_games['Age'] = olympics_games['Age'].astype(pd.Int64Dtype())
olympics_games['Height'] = olympics_games['Height'].astype(pd.Int64Dtype())
olympics_games['Weight'] = olympics_games['Weight'].astype(pd.Int64Dtype())
olympics_games['Name'] = olympics_games['Name'].astype(pd.StringDtype())
olympics_games['Sex'] = olympics_games['Sex'].astype(pd.StringDtype())
olympics_games['Team'] = olympics_games['Team'].astype(pd.StringDtype())
olympics_games['NOC'] = olympics_games['NOC'].astype(pd.StringDtype())
olympics_games['Region'] = olympics_games['Region'].astype(pd.StringDtype())
olympics_games['Games'] = olympics_games['Games'].astype(pd.StringDtype())
olympics_games['Season'] = olympics_games['Season'].astype(pd.StringDtype())
olympics_games['Sport'] = olympics_games['Sport'].astype(pd.StringDtype())
olympics_games['Event'] = olympics_games['Event'].astype(pd.StringDtype())
olympics_games['City'] = olympics_games['City'].astype(pd.StringDtype())
olympics_games['Country'] = olympics_games['Country'].astype(pd.StringDtype())
olympics_games['Medal'] = olympics_games['Medal'].astype(pd.StringDtype())
olympics_games['Notes'] = olympics_games['Notes'].astype(pd.StringDtype())


olympics_games.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 271116 entries, 0 to 271115
Data columns (total 18 columns):
 #   Column   Non-Null Count   Dtype 
---  ------   --------------   ----- 
 0   ID       271116 non-null  int64 
 1   Name     271116 non-null  string
 2   Sex      271116 non-null  string
 3   Age      261642 non-null  Int64 
 4   Height   210945 non-null  Int64 
 5   Weight   207183 non-null  Int64 
 6   Team     271116 non-null  string
 7   NOC      271116 non-null  string
 8   Region   271102 non-null  string
 9   Games    271116 non-null  string
 10  Year     271116 non-null  int64 
 11  Season   271116 non-null  string
 12  Sport    271116 non-null  string
 13  Event    271116 non-null  string
 14  City     271116 non-null  string
 15  Country  271116 non-null  string
 16  Medal    39783 non-null   string
 17  Notes    5039 non-null    string
dtypes: Int64(3), int64(2), string(13)
memory usage: 38.0 MB


olympics_games


# data without duplicated rows
olympics_games_distinct = (pysqldf(""" SELECT DISTINCT
                            ID, Name, Sex, Age, Height, Weight, Team, NOC, Region, Games, Year, Season, Sport, Event, City, Country, Medal, Notes
                    FROM olympics_games """))
olympics_games_distinct


# Determine duplicate rows in original data 
display(pysqldf(""" SELECT DISTINCT
                            ID, Name, Sex, Age, Height, Weight, Team, NOC, Region, Games, Year, Season, Sport, Event, City, Country, Medal, Notes
                    FROM olympics_games
                    GROUP BY ID, Name, Sex, Age, Height, Weight, Team, NOC, Region, Games, Year, Season, Sport, Event, City, Country, Medal, Notes
                    HAVING COUNT(ID) > 1"""))


display(pysqldf(""" SELECT * 
                    FROM olympics_games
                    WHERE ID = 2449"""))


# total number of observations in the data
display(pysqldf("SELECT COUNT(*)  FROM olympics_games; "))


# total number of observations in the data without duplicated rows 
display(pysqldf("SELECT COUNT(*)  FROM olympics_games_distinct; "))


# count null values in the data
display(pysqldf("""SELECT 
                        COUNT(*)-COUNT(ID) As ID, 
                        COUNT(*)-COUNT(Name) As Name, 
                        COUNT(*)-COUNT(Sex) As Sex, 
                        COUNT(*)-COUNT(Age) As Age, 
                        COUNT(*)-COUNT(Height) As Height, 
                        COUNT(*)-COUNT(Weight) As Weight, 
                        COUNT(*)-COUNT(Team) As Team,
                        COUNT(*)-COUNT(NOC) As NOC,
                        COUNT(*)-COUNT(Region) As Region,
                        COUNT(*)-COUNT(Games) As Games,
                        COUNT(*)-COUNT(Year) As Year,
                        COUNT(*)-COUNT(Season) As Season,
                        COUNT(*)-COUNT(Sport) As Sport,
                        COUNT(*)-COUNT(Event) As Event,
                        COUNT(*)-COUNT(City) As City,
                        COUNT(*)-COUNT(Country) As Country,
                        COUNT(*)-COUNT(Medal) As Medal,
                        COUNT(*)-COUNT(Notes) As Notes
                    FROM olympics_games_distinct;  """))


# null regions (Refugee and Unknown teams)
display(pysqldf("SELECT * FROM olympics_games_distinct WHERE Region IS NULL; "))


# count number of distinct values in the data
display(pysqldf(""" SELECT
                        COUNT(DISTINCT ID) AS IDs, 
                        COUNT(DISTINCT Name) AS Names,
                        COUNT(DISTINCT Sex) AS Sex, 
                        COUNT(DISTINCT Team) AS Teams, 
                        COUNT(DISTINCT NOC) AS NOCs, 
                        COUNT(DISTINCT Region) AS Regions, 
                        COUNT(DISTINCT Sport) AS Sports, 
                        COUNT(DISTINCT Event) AS Events, 
                        COUNT(DISTINCT Games) AS Games, 
                        COUNT(DISTINCT City) AS Cities, 
                        COUNT(DISTINCT Country) AS Countries, 
                        COUNT(DISTINCT Year) AS Years, 
                        COUNT(DISTINCT Season) AS Season,
                        COUNT(DISTINCT Medal) AS Medals
                    FROM olympics_games_distinct; """))


%%time
# Total number of medals per gender  
display(pysqldf(""" SELECT
                        Sex, 
                        COUNT(Medal) AS total_medal
                    FROM olympics_games_distinct
                    GROUP BY Sex; """))

CPU times: total: 4.39 s
Wall time: 4.39 s


%%time
# Total number of medals per gender using window funtion 
display(pysqldf(""" SELECT
                        DISTINCT Sex, 
                        COUNT(Medal) OVER(PARTITION BY Sex) AS total_medal
                    FROM olympics_games_distinct; """))

CPU times: total: 4.88 s
Wall time: 4.89 s


%%time
# Total number of gold, silver, bronze medals per sex
display(pysqldf(""" SELECT
                        Sex, 
                        Medal, 
                        COUNT(Medal) AS total_medal
                    FROM olympics_games_distinct
                    WHERE Medal IS NOT NULL
                    GROUP BY Sex, Medal; """))

CPU times: total: 4.39 s
Wall time: 4.38 s


%%time
# Total number of gold, silver, bronze medals per sex using window function 
display(pysqldf(""" SELECT
                        DISTINCT Sex, MedaL, 
                        COUNT(Medal) OVER(PARTITION BY Sex, Medal)AS total_medal
                    FROM olympics_games_distinct
                    WHERE Medal IS NOT NULL; """))

CPU times: total: 4.69 s
Wall time: 4.69 s


# count how many distinct IDs each name belongs to
display(pysqldf(""" SELECT Name, COUNT(DISTINCT ID) AS id_count 
                    FROM olympics_games_distinct
                    GROUP BY Name
                    ORDER BY id_count DESC  """))


%%time
#Gender distribution in sports
gender_per_sport = pysqldf(""" SELECT 
                        Sport, Sex, COUNT(DISTINCT ID) as gender_count
                    FROM olympics_games_distinct
                    GROUP BY Sport, Sex
                    /*ORDER BY gender_count DESC*/""")

gender_per_sport

CPU times: total: 4.84 s
Wall time: 4.85 s


#Gender distribution in sports
gender_per_sport = pysqldf(""" SELECT  
                                      Sport, 
                                      COUNT(CASE WHEN Sex = 'M' THEN 1 END) AS male_count, 
                                      COUNT(CASE WHEN Sex = 'F' THEN 1 END) AS female_count
                                FROM(
                                        SELECT DISTINCT ID, Name, Sex, Sport 
                                        FROM olympics_games_distinct
                                        )
                                GROUP BY Sport  """)

gender_per_sport


#Count number of home and away teams' medals in each game 
medals_count_df = pysqldf(""" 
                    SELECT Games, 
                           TeamType, 
                           COUNT(Medal) AS medal_count
                    FROM (  SELECT DISTINCT ID, 
                                    Name, 
                                    Games,
                                    Event,
                                    Sport, 
                                    Team, 
                                    Region, 
                                    Country, 
                                    Medal,
                                    (CASE WHEN Region != Country THEN 'away' 
                                          WHEN Region = Country THEN 'home'
                                          END) AS TeamType
                            FROM olympics_games_distinct
                            WHERE Medal IS NOT NULL)
                    GROUP BY Games, TeamType
                """)
medals_count_df


# number of teams in every game 
display(pysqldf(""" SELECT Games, COUNT(DISTINCT Team) as num_teams 
                    FROM olympics_games_distinct
                    GROUP BY Games 
                    ;"""))


# number of home and away teams in every game
homeaway_teamcnt_df = pysqldf("""  WITH hometeams AS( SELECT Games, COUNT(DISTINCT Team) AS home_team_cnt 
                                                      FROM olympics_games_distinct
                                                      WHERE Region=Country
                                                      GROUP BY Games)
                                    SELECT *
                                    FROM hometeams
                                    LEFT JOIN( SELECT Games, COUNT(DISTINCT Team) AS away_team_cnt 
                                               FROM olympics_games_distinct
                                               WHERE Region!=Country
                                               GROUP BY Games)
                                    USING(Games)
                                """)
homeaway_teamcnt_df


# only 1 home team in 1980 summer?
display(pysqldf(""" SELECT DISTINCT Team
                    FROM olympics_games_distinct
                    WHERE Games = '1980 Summer'  AND (Region = Country) """))


# home teams in 1896 Summer 
display(pysqldf(""" SELECT DISTINCT Team
                    FROM olympics_games_distinct
                    WHERE Games = '1896 Summer'  AND (Region = Country)
                   """))


# verify results above 
display(pysqldf(""" SELECT DISTINCT Games, Team, Region, Country
                    FROM olympics_games_distinct
                    WHERE Games = '1904 Summer' AND Region = Country ; """))


#check why there are no home teams in 1984 winter (Yugoslavia is now Serbia) (yusgoslavia country changed to serbia in databricks workspace)
#display(pysqldf(""" SELECT DISTINCT Games, Team, Region, Country
    #                FROM olympics_games_distinct
   #                 WHERE Games = '1984 Winter' /*AND Region = 'UK'*/; """))


#Count number of home and away teams' medals in each game 
medals_count_df = pysqldf(""" 
                    SELECT Games, 
                           AwayHome, 
                           COUNT(Medal) AS medal_count 
                    FROM (  SELECT DISTINCT ID, 
                                    Name, 
                                    Games,
                                    Event,
                                    Sport, 
                                    Team, 
                                    Region, 
                                    Country, 
                                    Medal,
                                    (CASE WHEN Region != Country THEN 'away' 
                                          WHEN Region = Country THEN 'home'
                                          END) AS AwayHome
                            FROM olympics_games_distinct )
                    GROUP BY Games, AwayHome
                """)
medals_count_df


# Count total number of away teams' medals and home teams medals
display(pysqldf(""" SELECT AwayHome, 
                           SUM(medal_count) as total_medal_count  
                    FROM medals_count_df
                    GROUP BY AwayHome """))


home_medal_df = pysqldf(""" SELECT Games, medal_count AS home_medal_count 
                            FROM medals_count_df 
                            WHERE AwayHome = 'home'
                            """)
away_medal_df = pysqldf(""" SELECT Games, medal_count AS away_medal_count 
                            FROM medals_count_df 
                            WHERE AwayHome = 'away'
                            """)


# put home and away team medal count into separate columns 
homeaway_df = pysqldf(""" SELECT * 
                          FROM home_medal_df 
                          LEFT JOIN away_medal_df
                          USING(Games)
                            """)


# join total number of home/away medals and teams 
homeaway_teammedal_df = pysqldf("""   SELECT * 
                                      FROM homeaway_df 
                                      LEFT JOIN homeaway_teamcnt_df
                                      USING(Games) """)
homeaway_teammedal_df


# function to compute descriptive stats 
def descriptive_stats(table_name, table_col): 
    print("**************************************************")
    print(f"Table Name: {table_name}")
    print(f"Table Column: {table_col}")
    print("**************************************************")

    print("")

    # Count
    cnt = pysqldf(f"""SELECT 
                            "COUNT" AS Stat, 
                            COUNT({table_col}) AS Value 
                      FROM {table_name}; """)

    # Mean 
    mean = pysqldf(f"""SELECT 
                            "MEAN" AS Stat, 
                            CAST(AVG({table_col}) AS INT) AS Value 
                        FROM {table_name};""")
    # Median 
    median = pysqldf(f"""SELECT 
                            "MEDIAN" AS Stat,
                            {table_col} AS Value 
                        FROM {table_name}
                        ORDER BY {table_col}
                        LIMIT 1
                        OFFSET (SELECT COUNT(*) FROM {table_name}) / 2 ;""")
    # Mode 
    mode = pysqldf(f"""SELECT 
                            "MODE" AS Stat,
                            {table_col} AS Value,
                            COUNT(*) AS Count
                        FROM {table_name}
                        WHERE {table_col} IS NOT NULL
                        GROUP BY {table_col}
                        ORDER BY COUNT(*) DESC
                        LIMIT 1""")
    # Min and Max 
    min = pysqldf(f"""SELECT 
                            "MIN" AS Stat, 
                            MIN({table_col}) AS Value 
                      FROM {table_name}; """)
    max = pysqldf(f"""SELECT 
                            "MAX" AS Stat, 
                            MAX({table_col}) AS Value 
                      FROM {table_name}; """)
    
    display(pd.concat([cnt, mean, median, min, max]).set_index("Stat"))
    display(mode.set_index('Stat'))
    print("")


descriptive_stats('olympics_games_distinct', 'olympics_games_distinct.Height')

**************************************************
Table Name: olympics_games_distinct
Table Column: olympics_games_distinct.Height
**************************************************


descriptive_stats('olympics_games_distinct', 'olympics_games_distinct.Age')

**************************************************
Table Name: olympics_games_distinct
Table Column: olympics_games_distinct.Age
**************************************************


descriptive_stats('olympics_games_distinct', 'olympics_games_distinct.Weight')

**************************************************
Table Name: olympics_games_distinct
Table Column: olympics_games_distinct.Weight
**************************************************


# For 25-50-75-100 quartile range in SQL 

def get_iqrs(table_name, table_col):
    query = f"""WITH percent_tbl AS (SELECT 
                                            {table_col}, 
                                            ntile(4) OVER (ORDER BY {table_col}) percent
                                            FROM {table_name}), 
                     p_25 AS (SELECT 
                                    (percent * 0.25) as pct, 
                                    last_value({table_col}) OVER (PARTITION BY percent) last_val
                               FROM percent_tbl
                               WHERE percent =  1 
                               LIMIT 1), 
                     p_50 AS (SELECT 
                                    (percent * 0.25) as pct, 
                                    last_value({table_col}) OVER (PARTITION BY percent) last_val
                               FROM percent_tbl
                               WHERE percent =  2 
                               LIMIT 1),  
                     p_75 AS (SELECT 
                                    (percent * 0.25) as pct, 
                                    last_value({table_col}) OVER (PARTITION BY percent) last_val
                               FROM percent_tbl
                               WHERE percent =  3 
                               LIMIT 1), 
                     p_100 AS (SELECT 
                                    (percent * 0.25) as pct, 
                                    last_value({table_col}) OVER (PARTITION BY percent) last_val
                               FROM percent_tbl
                               WHERE percent =  4 
                               LIMIT 1)
                     SELECT * FROM p_25 UNION
                     SELECT * FROM p_50 UNION
                     SELECT * FROM p_75 UNION
                     SELECT * FROM p_100;"""
    iqrs = pysqldf(query)
    return display(pd.DataFrame(iqrs))


get_iqrs('olympics_games_distinct', 'Age')


get_iqrs('olympics_games_distinct', 'Height')


get_iqrs('olympics_games_distinct', 'Weight')


descriptive_stats('olympics_games_distinct', 'olympics_games_distinct.ID')

**************************************************
Table Name: olympics_games_distinct
Table Column: olympics_games_distinct.ID
**************************************************


# ID with the most number of observations in the data
display(pysqldf(""" SELECT ID, Name, COUNT(DISTINCT Games) as num_games 
                    FROM olympics_games_distinct 
                    WHERE ID = 106296; """))


# Most common ID/Athlete observations in the data based on the number of events they participated in 
display(pysqldf(""" SELECT ID, Name, COUNT(DISTINCT Event) as num_events
                    FROM olympics_games_distinct 
                    WHERE ID = 106296; """))


# Most common ID/Athlete observations in the data 
display(pysqldf(""" SELECT ID, Name, COUNT(*) as num_of_observations 
                    FROM olympics_games_distinct 
                    GROUP BY ID, Name
                    ORDER BY num_of_observations DESC; """))


display(pysqldf(""" SELECT *
                    FROM olympics_games_distinct
                    WHERE Name = 'Heikki Ilmari Savolainen'; """))


# Most common ID/Athlete based on number of distinct games played 
display(pysqldf(""" SELECT ID, Name, COUNT(DISTINCT Games) as num_games,  COUNT(DISTINCT Sport) as num_sports_played
                    FROM olympics_games_distinct 
                    WHERE Games IS NOT NULL
                    GROUP BY ID, Name
                    ORDER BY num_games DESC; """))


# Average number of games played by athletes in the Olympics overall
display(pysqldf(""" SELECT AVG(num_games) AS avg_num_games
                    FROM(
                        SELECT ID, Name, COUNT(DISTINCT Games) as num_games,  COUNT(DISTINCT Sport) as num_sports
                        FROM olympics_games_distinct 
                        WHERE Games IS NOT NULL
                        GROUP BY ID, Name
                        ORDER BY num_games DESC) """))


# Number of games and number of sports played by each athlete
display(pysqldf(""" SELECT ID, Name,   COUNT(DISTINCT Sport) as num_sports, COUNT(DISTINCT Games) as num_games
                    FROM olympics_games_distinct 
                    WHERE Games IS NOT NULL
                    GROUP BY ID, Name
                    ORDER BY num_sports DESC""" ))


display(pysqldf(""" SELECT ID, Name, Games, Sport
                    FROM olympics_games_distinct 
                    WHERE ID = 6386
                    """ ))


# How many games do athletes in the Olympics commonly play? Do they tend to go back for another game? 
# Observation: only 1 athlete played 10 different olympics games 
display(pysqldf(""" 
                        SELECT
                            num_games,
                            COUNT(*) as athlete_cnt
                        FROM(
                                SELECT ID, Name, COUNT(DISTINCT Games) as num_games
                                FROM olympics_games_distinct 
                                WHERE Games IS NOT NULL
                                GROUP BY ID, Name)
                        GROUP BY num_games  """ ))


# get separate male and female records 
female_df = pysqldf("""SELECT * FROM olympics_games_distinct WHERE Sex = 'F'; """)
male_df = pysqldf("""SELECT * FROM olympics_games_distinct WHERE Sex = 'M'; """)


descriptive_stats('female_df', 'female_df.Weight')

**************************************************
Table Name: female_df
Table Column: female_df.Weight
**************************************************


descriptive_stats('male_df', 'male_df.Weight')

**************************************************
Table Name: male_df
Table Column: male_df.Weight
**************************************************


get_iqrs('female_df', 'Height')


get_iqrs('male_df', 'Height')


# number of specific sports each athlete participated in 
display(pysqldf(""" SELECT 
                            ID, Name, COUNT(DISTINCT Sport) as num_sports 
                    FROM olympics_games_distinct 
                    WHERE Sport IS NOT NULL
                    GROUP BY ID, Name
                    ORDER BY num_sports DESC; """))


# number of sports each female athlete participate in 
display(pysqldf(""" SELECT ID, Name, COUNT(DISTINCT Sport) as num_sports 
                    FROM female_df 
                    WHERE Sport IS NOT NULL
                    GROUP BY ID, Name
                    ORDER BY num_sports DESC"""))


# number female athletes who participate in more than 1 sport 
display(pysqldf(""" SELECT COUNT(*) AS female_2ormore 
                    FROM(
                        SELECT ID, Name, COUNT(DISTINCT Sport) as num_sports 
                        FROM female_df 
                        WHERE Sport IS NOT NULL
                        GROUP BY ID, Name
                        ORDER BY num_sports DESC)
                    WHERE num_sports > 1"""))


# number of sports each male athlete participate in 
display(pysqldf(""" SELECT ID, Name, COUNT(DISTINCT Sport) as num_sports 
                    FROM male_df 
                    WHERE Sport IS NOT NULL
                    GROUP BY ID, Name
                    ORDER BY num_sports DESC; """))


# number male athletes who play more than 1 sport 
display(pysqldf(""" SELECT COUNT(*) AS male_2ormore 
                    FROM(
                        SELECT ID, Name, COUNT(DISTINCT Sport) as num_sports 
                        FROM male_df 
                        WHERE Sport IS NOT NULL
                        GROUP BY ID, Name
                        ORDER BY num_sports DESC)
                    WHERE num_sports > 1"""))


display(pysqldf("""SELECT * FROM male_df WHERE Sex = 'F'; """))


display(pysqldf("""SELECT * FROM female_df WHERE Sex = 'M'; """))


# Players with two genders (None) 
display(pysqldf(""" SELECT 
                        ID, Name, COUNT(DISTINCT Sex) AS gender_count 
                    FROM olympics_games_distinct 
                    GROUP BY ID, Name
                    ORDER BY gender_count DESC; """))


# number of sports played by every atlete in each region 
display(pysqldf(""" SELECT Region, ID, Name, COUNT(DISTINCT Sport) as num_sports 
                    FROM olympics_games_distinct  
                    WHERE Sport IS NOT NULL
                    GROUP BY Region, ID, Name
                    ORDER BY num_sports DESC"""))


# region with the most versatile athletes based on the number of sports they play
display(pysqldf(""" SELECT Region, COUNT(*) AS ath_count 
                    FROM(
                        SELECT Region, ID, Name, COUNT(DISTINCT Sport) as num_sports 
                        FROM olympics_games_distinct  
                        WHERE Sport IS NOT NULL
                        GROUP BY Region, ID, Name
                        ORDER BY num_sports DESC)
                    WHERE num_sports > 1
                    GROUP BY Region
                    ORDER BY ath_count DESC"""))


# Percent difference between total number of male and female athletes in the Olympics  
display(pysqldf(""" SELECT Sex, COUNT(DISTINCT ID) AS count 
                    FROM olympics_games_distinct
                    GROUP BY Sex; """))


# Percent difference between total number of male and female athletes  
((abs(101590-33981)) / ((101590+33981)/2))*100

99.73961983019967


# add team_type column (away or home)
olympics_games_distdf = pysqldf(""" 
                                    SELECT 
                                          ID,Name,Sex,Age,Height,Weight,Team,NOC,Region,Games,Year,Season,Sport,Event,City,Country,Medal,Notes, 
                                          (CASE WHEN Region != Country THEN 'away' 
                                                WHEN Region = Country THEN 'home'
                                                END) AS TeamType
                                    FROM olympics_games_distinct; 
                                """)
olympics_games_distdf


# number of home and away teams in every game
display(pysqldf("""  WITH hometeams AS( SELECT Games, COUNT(DISTINCT Team) AS home_team_cnt 
                                                      FROM olympics_games_distinct
                                                      WHERE Region=Country
                                                      GROUP BY Games)
                                    SELECT *
                                    FROM hometeams
                                    LEFT JOIN( SELECT Games, COUNT(DISTINCT Team) AS away_team_cnt 
                                               FROM olympics_games_distinct
                                               WHERE Region!=Country
                                               GROUP BY Games)
                                    USING(Games)
                                """))


# combined medal count for home and away teams per Olympics game 
display(pysqldf("""   SELECT *
                      FROM( SELECT Games, COUNT(Medal) AS home_medal_count 
                            FROM olympics_games_distdf
                            WHERE TeamType = 'home'
                            GROUP BY Games)
                     LEFT JOIN( SELECT Games, COUNT(Medal) AS away_medal_count 
                                FROM olympics_games_distdf
                                WHERE TeamType = 'away'
                                GROUP BY Games)
                    USING(Games); 
                """))


# medal count for home and away teams per Olympics game 
medalcnt_awayhome_df = pysqldf("""  WITH teams_count AS(SELECT *
                                                        FROM (SELECT Games, COUNT(DISTINCT Team) AS home_team_cnt 
                                                              FROM olympics_games_distinct
                                                              WHERE Region=Country
                                                              GROUP BY Games)
                                                        LEFT JOIN( SELECT Games, COUNT(DISTINCT Team) AS away_team_cnt 
                                                                   FROM olympics_games_distinct
                                                                   WHERE Region!=Country
                                                                   GROUP BY Games)
                                                        USING(Games) ) 
                                    SELECT * 
                                    FROM teams_count
                                    LEFT JOIN (
                                                  SELECT *
                                                  FROM( SELECT Games, COUNT(Medal) AS home_medal_count 
                                                        FROM olympics_games_distdf
                                                        WHERE TeamType = 'home'
                                                        GROUP BY Games)
                                                  LEFT JOIN( SELECT Games, COUNT(Medal) AS away_medal_count 
                                                            FROM olympics_games_distdf
                                                            WHERE TeamType = 'away'
                                                            GROUP BY Games)
                                                 USING(Games)
                                                )
                                    USING(Games)
                """)
medalcnt_awayhome_df


medalavg_awayhome_df = pysqldf(""" SELECT 
                                        Games, home_team_cnt,away_team_cnt,home_medal_count,away_medal_count,
                                        CAST(home_medal_count AS float) / CAST(home_team_cnt AS float) AS home_avgmedal, 
                                        CAST(away_medal_count AS float) / CAST(away_team_cnt AS float) AS away_avgmedal 
                                   FROM medalcnt_awayhome_df; """)
medalavg_awayhome_df


#Count number of  home and away distinct atheletes in each game 
homeaway_count_df = pysqldf(""" 
                    SELECT Games, 
                           COUNT(CASE WHEN Region != Country THEN 1 END) AS away_athletes_count,  
                           COUNT(CASE WHEN Region = Country THEN 1 END) AS home_athletes_count
                    FROM (  SELECT DISTINCT Games, Team, Region, Country, ID
                            FROM olympics_games_distinct)
                    GROUP BY Games; 
                """)
homeaway_count_df


#count number of games where  home medals > away team medals
display(pysqldf("""
                    SELECT COUNT(*) AS count 
                    FROM medalavg_awayhome_df
                    WHERE home_medal_count < away_medal_count
                """))


#count number of games where home medals > away team medals
display(pysqldf("""
                    SELECT COUNT(*) AS count 
                    FROM medalavg_awayhome_df
                    WHERE home_medal_count > away_medal_count
                """))


#count number of games where avg home team medals < away team medals
display(pysqldf("""
                    SELECT *
                    FROM medalavg_awayhome_df
                    WHERE home_avgmedal < away_avgmedal
                """))


#count games where avg home team medals > away team medals
display(pysqldf("""
                    SELECT *
                    FROM medalavg_awayhome_df
                    WHERE home_avgmedal > away_avgmedal
                """))


#count number of games where avg home medals < away team medals
display(pysqldf("""
                    SELECT *
                    FROM medalavg_awayhome_df
                    WHERE home_avgmedal < away_avgmedal
                """))


# medal count for home and away teams per Olympics game 
goldcnt_awayhome_df = pysqldf("""  WITH teams_count AS(SELECT *
                                                        FROM (SELECT Games, COUNT(DISTINCT Team) AS home_team_cnt 
                                                              FROM olympics_games_distinct
                                                              WHERE Region=Country
                                                              GROUP BY Games)
                                                        LEFT JOIN( SELECT Games, COUNT(DISTINCT Team) AS away_team_cnt 
                                                                   FROM olympics_games_distinct
                                                                   WHERE Region!=Country
                                                                   GROUP BY Games)
                                                        USING(Games) ) 
                                    SELECT * 
                                    FROM teams_count
                                    LEFT JOIN (
                                                  SELECT *
                                                  FROM( SELECT Games, COUNT(Medal) AS away_gold_count 
                                                        FROM olympics_games_distdf
                                                        WHERE (Medal = "Gold") AND (TeamType = 'away')
                                                        GROUP BY Games)
                                                  LEFT JOIN( SELECT Games, CAST(COUNT(Medal) AS INT) AS home_gold_count 
                                                            FROM olympics_games_distdf
                                                            WHERE  (Medal = "Gold") AND (TeamType = 'home')
                                                            GROUP BY Games)
                                                 USING(Games)
                                                )
                                    USING(Games)
                """)
goldcnt_awayhome_df


# check Nulls in results above 
display(pysqldf("""SELECT * FROM olympics_games_distdf WHERE Games = '1924 Winter' AND Medal = 'Gold' AND TeamType = 'home'"""))


# Replace NaNs to 0 
goldcnt_awayhome_df = pysqldf(""" SELECT Games,home_team_cnt,away_team_cnt,away_gold_count,
                                          (CASE WHEN home_gold_count IS NULL THEN 0 ELSE home_gold_count END) AS home_gold_count
                                   FROM goldcnt_awayhome_df
                                """)
goldcnt_awayhome_df


goldcnt_awayhome_df = pysqldf(""" SELECT 
                                        Games, home_team_cnt,away_team_cnt,home_gold_count,away_gold_count,
                                        CAST(home_gold_count AS float) / CAST(home_team_cnt AS float) AS home_avggoldmedal, 
                                        CAST(away_gold_count AS float) / CAST(away_team_cnt AS float) AS away_avggoldmedal 
                                   FROM goldcnt_awayhome_df; """)
goldcnt_awayhome_df


#Count number of  home and away teams' gold medals in each game 
awayhome_gold_df = (pysqldf(""" 
                    SELECT Games,
                           AwayHome, 
                           COUNT(Medal) AS gold_medal_count
                    FROM (  SELECT DISTINCT ID, 
                                    Name, 
                                    Games,
                                    Event,
                                    Sport, 
                                    Team, 
                                    Region, 
                                    Country, 
                                    Medal,
                                    (CASE WHEN Region != Country THEN 'away' 
                                          WHEN Region = Country THEN 'home'
                                          END) AS AwayHome
                            FROM olympics_games_distinct
                            WHERE Medal = 'Gold')
                    GROUP BY Games, AwayHome
                """))
awayhome_gold_df


#Count number of  home and away teams' silver medals in each game 
display(pysqldf(""" 
                    SELECT Games,
                           AwayHome, 
                           COUNT(Medal) AS silver_medal_count
                    FROM (  SELECT DISTINCT ID, 
                                    Name, 
                                    Games,
                                    Event,
                                    Sport, 
                                    Team, 
                                    Region, 
                                    Country, 
                                    Medal,
                                    (CASE WHEN Region != Country THEN 'away' 
                                          WHEN Region = Country THEN 'home'
                                          END) AS AwayHome
                            FROM olympics_games_distinct
                            WHERE Medal = 'Silver')
                    GROUP BY Games, AwayHome
                """))


#Count number of  home and away teams' bronze medals in each game 
display(pysqldf(""" 
                    SELECT Games,
                           AwayHome, 
                           COUNT(Medal) AS bronze_medal_count
                    FROM (  SELECT DISTINCT ID, 
                                    Name, 
                                    Games,
                                    Event,
                                    Sport, 
                                    Team, 
                                    Region, 
                                    Country, 
                                    Medal,
                                    (CASE WHEN Region != Country THEN 'away' 
                                          WHEN Region = Country THEN 'home'
                                          END) AS AwayHome
                            FROM olympics_games_distinct
                            WHERE Medal = 'Bronze')
                    GROUP BY Games,  AwayHome
                """))


#Count number of  home and away teams' medals in each game 
goldsilverbronze_count_df = pysqldf(""" 
                    SELECT Games, 
                           AwayHome, 
                           Medal, 
                           COUNT(Medal) AS medal_count
                    FROM (  SELECT DISTINCT ID, 
                                    Name, 
                                    Games,
                                    Event,
                                    Sport, 
                                    Team, 
                                    Region, 
                                    Country, 
                                    Medal,
                                    (CASE WHEN Region != Country THEN 'away' 
                                          WHEN Region = Country THEN 'home'
                                          END) AS AwayHome
                            FROM olympics_games_distinct
                            WHERE Medal IS NOT NULL)
                    GROUP BY Games, AwayHome,Medal
                """)
goldsilverbronze_count_df


# dataframe for athletes 
athletes_df = pysqldf("""SELECT ID, Name, Sex, Age, Height, Weight, Team, Games, Medal, COUNT(Medal) AS medal_count 
                            FROM olympics_games_distinct
                            GROUP BY ID, Name, Sex, Age, Height, Weight, Team, Games, Medal
                        """)
athletes_df


# athlete who participated the most in different olympics games 
display(pysqldf("""SELECT *
                   FROM athletes_df
                   WHERE Name = 'Ian Millar'"""))


#athlete with the highest number of medals 
athlete_medals_df = (pysqldf("""SELECT ID, Name, Sex, AVG(Age) AS avg_age, AVG(Height) AS avg_height, AVG(Weight) AS avg_weight,  SUM(medal_count) AS total_medal 
                   FROM athletes_df
                   GROUP BY  ID, Name
                   ORDER BY total_medal DESC"""))
athlete_medals_df


# count null values in avg age, height, and weight 
display(pysqldf("""SELECT
                        COUNT(*)-COUNT(Name) As Name, 
                        COUNT(*)-COUNT(Sex) As Sex, 
                        COUNT(*)-COUNT(avg_age) As avg_age, 
                        COUNT(*)-COUNT(avg_height) As avg_height, 
                        COUNT(*)-COUNT(avg_weight) As avg_weight, 
                        COUNT(*)-COUNT(total_medal) As total_medal
                    FROM athlete_medals_df;  """))


# Replace null values in each column with average 
athlete_medals_df = athlete_medals_df.fillna(athlete_medals_df[['avg_age','avg_height','avg_weight']].mean())


# recheck number of null values 
display(pysqldf("""SELECT
                        COUNT(*)-COUNT(Name) As Name, 
                        COUNT(*)-COUNT(Sex) As Sex, 
                        COUNT(*)-COUNT(avg_age) As avg_age, 
                        COUNT(*)-COUNT(avg_height) As avg_height, 
                        COUNT(*)-COUNT(avg_weight) As avg_weight, 
                        COUNT(*)-COUNT(total_medal) As total_medal
                    FROM athlete_medals_df;  """))


athletes_gold_df = pysqldf(""" SELECT ID, Name, Sex, AVG(Age) AS avg_age, AVG(Height) AS avg_height, AVG(Weight) AS avg_weight,  SUM(goldmedal_count) AS total_goldmedal 
                           FROM(   
                                SELECT ID, Name, Sex, Age, Height, Weight, Team, Games, Medal, COUNT(Medal) AS goldmedal_count 
                                FROM olympics_games_distinct
                                WHERE Medal= "Gold"
                                GROUP BY ID, Name, Sex, Age, Height, Weight, Team, Games, Medal) 
                           GROUP BY  ID, Name
                           ORDER BY total_goldmedal DESC
                        """)
athletes_gold_df


# Replace null values in each column with average 
athletes_gold_df = athletes_gold_df.fillna(athletes_gold_df[['avg_age','avg_height','avg_weight']].mean())


display(pysqldf(""" SELECT Sex, Sport, COUNT(Medal) AS medal_count
                    FROM olympics_games_distinct
                    GROUP BY Sport, Sex;"""))


# count of male and female pariticpants in each sport 
display(pysqldf(""" SELECT Sport,
                            COUNT(CASE WHEN Sex = 'M' Then 1 END) AS male_cnt, 
                            COUNT(CASE WHEN Sex = 'F' Then 1 END) AS female_cnt
                    FROM olympics_games_distinct
                    GROUP BY Sport; 
                    """))


#sports where only one gender participate in 
display(pysqldf(""" SELECT  * 
                    FROM(SELECT Sport,
                                COUNT(CASE WHEN Sex = 'M' Then 1 END) AS male_cnt, 
                                COUNT(CASE WHEN Sex = 'F' Then 1 END) AS female_cnt
                        FROM olympics_games_distinct
                        GROUP BY Sport)
                    WHERE male_cnt = 0 
                    """))


#sports where only one gender participate in 
display(pysqldf("""SELECT *
                   FROM(
                        SELECT Sport, Sex, COUNT(DISTINCT Sex) as gender_count
                        FROM olympics_games_distinct
                        GROUP BY Sport)
                    WHERE gender_count = 1; 
                    """))


#sports where only female participate in 
display(pysqldf("""SELECT *
                   FROM(
                        SELECT Sport, Sex, COUNT(DISTINCT Sex) as gender_count
                        FROM olympics_games_distinct
                        GROUP BY Sport)
                    WHERE gender_count = 1 AND Sex = 'F'; 
                    """))


#sports where only male participate in 
display(pysqldf("""SELECT *
                   FROM(
                        SELECT Sport, Sex, COUNT(DISTINCT Sex) as dist_gender_count
                        FROM olympics_games_distinct
                        GROUP BY Sport)
                    WHERE dist_gender_count = 1 AND Sex = 'M'; 
                    """))


#sports where both gender participate 
mixedsex_sport_df = (pysqldf(""" SELECT *
                    FROM(
                        SELECT Sex, Sport, 
                               COUNT(Medal) AS medal_count
                        FROM olympics_games_distinct
                        GROUP BY Sport, Sex)
                    WHERE Sport NOT IN(SELECT Sport 
                                       FROM(
                                            SELECT Sport, Sex, COUNT(DISTINCT Sex) as gender_count
                                            FROM olympics_games_distinct
                                            GROUP BY Sport)
                                        WHERE gender_count = 1)
                        ;"""))
mixedsex_sport_df


display(pysqldf("""   SELECT *
                      FROM(
                          SELECT Sport, medal_count AS female_medal_cnt 
                          FROM mixedsex_sport_df
                          WHERE Sex = 'F')
                      LEFT JOIN ( SELECT Sport, medal_count AS male_medal_cnt 
                                  FROM mixedsex_sport_df
                                  WHERE Sex = 'M')
                      USING(Sport)"""))


#Count number of  male and female athletes in each sport 
sportgender_count_df = pysqldf(""" 
                    SELECT Sport, 
                           COUNT(CASE WHEN Sex ='F' THEN 1 END) AS female_ath_count,  
                           COUNT(CASE WHEN Sex ='M' THEN 1 END) AS male_ath_count
                    FROM ( SELECT DISTINCT ID, Sex, Sport
                           FROM olympics_games_distinct)
                    GROUP BY Sport
                """)
sportgender_count_df


#only 1 aeronautic record? 
display(pysqldf(""" SELECT *
                            FROM olympics_games_distinct
                            where Sport = 'Aeronautics'  """))


# verify result above 
display(pysqldf(""" SELECT *
                            FROM olympics_games
                            where Sport = 'Basque Pelota' """))


# exlcude sports where only one gender participate in 
mixedsport_athcount_df = pysqldf(""" SELECT *
                                     FROM sportgender_count_df
                                     WHERE female_ath_count > 0 AND male_ath_count > 0""")
mixedsport_athcount_df


# medal and athlete count  for each sport where both genders participate in 
sportgender_medal_df = pysqldf("""    SELECT * 
                                      FROM mixedsport_athcount_df 
                                      LEFT JOIN ( SELECT *
                                                  FROM(
                                                      SELECT Sport, medal_count AS female_medal_cnt 
                                                      FROM mixedsex_sport_df
                                                      WHERE Sex = 'F')
                                                  LEFT JOIN ( SELECT Sport, medal_count AS male_medal_cnt 
                                                              FROM mixedsex_sport_df
                                                              WHERE Sex = 'M')
                                                  USING(Sport))
                                      USING(Sport) """)
sportgender_medal_df


# medal count for male and female athletes for each sport where both genders participate in 
#sportgendermedal_df = pysqldf("""  WITH obs_count AS(
#                                                         SELECT 
#                                                            Sport, 
#                                                            COUNT(CASE WHEN Sex = 'F' THEN 1 END) AS femaleobs_count, 
#                                                            COUNT(CASE WHEN Sex = 'M' THEN 1 END) AS maleobs_count 
#                                                        FROM olympics_games_distdf 
#                                                        GROUP BY  Sport ) 
#                                    SELECT * 
#                                    FROM obs_count
#                                    LEFT JOIN gender_sportmedal_df
#                                    USING(Sport)
 #                                   WHERE femaleobs_count > 0 AND maleobs_count > 0 
#                                """)
#sportgendermedal_df


sportgendermedal_df = pysqldf(""" SELECT 
                                        Sport,female_ath_count,male_ath_count,female_medal_cnt,male_medal_cnt,
                                        CAST(female_medal_cnt AS float) / CAST(female_ath_count AS float) AS female_avgmedal, 
                                        CAST(male_medal_cnt AS float) / CAST(male_ath_count AS float) AS male_avgmedal 
                                   FROM sportgender_medal_df; """)
sportgendermedal_df


gendergold_sport_df = (pysqldf("""  SELECT *
                                    FROM(
                                        SELECT Sex, Sport, 
                                               COUNT(Medal) AS goldmedal_count
                                        FROM olympics_games_distinct
                                        WHERE Medal = 'Gold'
                                        GROUP BY Sport, Sex)
                                    WHERE Sport NOT IN(SELECT Sport 
                                                       FROM(
                                                            SELECT Sport, Sex, COUNT(DISTINCT Sex) as gender_count
                                                            FROM olympics_games_distinct
                                                            GROUP BY Sport)
                                                        WHERE gender_count = 1) ;
                                """))
gendergold_sport_df


# medal and athlete count  for each sport where both genders participate in 
sportgender_goldmedal_df = pysqldf("""    SELECT * 
                                      FROM mixedsport_athcount_df 
                                      LEFT JOIN ( SELECT *
                                                  FROM(
                                                      SELECT Sport, goldmedal_count AS male_goldmedal_cnt 
                                                      FROM gendergold_sport_df
                                                      WHERE Sex = 'M')
                                                  LEFT JOIN ( SELECT Sport, goldmedal_count AS female_goldmedal_cnt 
                                                              FROM gendergold_sport_df
                                                              WHERE Sex = 'F')
                                                  USING(Sport))
                                      USING(Sport) """)
sportgender_goldmedal_df


# Replace null values with 0
sportgender_goldmedal_df = pysqldf(""" SELECT Sport,female_ath_count,male_ath_count,male_goldmedal_cnt,
                                          (CASE WHEN female_goldmedal_cnt IS NULL THEN 0 ELSE female_goldmedal_cnt END) AS female_goldmedal_cnt
                                   FROM sportgender_goldmedal_df
                                """)
sportgender_goldmedal_df


sportgender_gold_df = pysqldf(""" SELECT 
                                        Sport,female_ath_count,male_ath_count,male_goldmedal_cnt,female_goldmedal_cnt,
                                        CAST(female_goldmedal_cnt AS float) / CAST(female_ath_count AS float) AS female_avggoldmedal, 
                                        CAST(male_goldmedal_cnt AS float) / CAST(male_ath_count AS float) AS male_avggoldmedal 
                                   FROM sportgender_goldmedal_df; """)
sportgender_gold_df


# describe overall age, height, weight
olympics_games_distinct[['Age', 'Height', 'Weight']].describe()


# describe age, height, weight for female athletes 
female_df[['Age', 'Height', 'Weight']].describe()


# describe age, height, weight for male athletes 
male_df[['Age', 'Height', 'Weight']].describe()


male_medals_df = pysqldf("""SELECT * FROM athlete_medals_df WHERE Sex = 'M'""")
male_medals_df.describe()


female_medals_df= pysqldf("""SELECT * FROM athlete_medals_df WHERE Sex = 'F'""")
female_medals_df.describe()


medalavg_awayhome_df.columns

Index(['Games', 'home_team_cnt', 'away_team_cnt', 'home_medal_count',
       'away_medal_count', 'home_avgmedal', 'away_avgmedal'],
      dtype='object')


# games where home medal count > away medal count 
medalavg_awayhome_df[medalavg_awayhome_df['home_medal_count'] > medalavg_awayhome_df['away_medal_count']]


# games where home teams count < away teams count 
medalavg_awayhome_df[medalavg_awayhome_df['home_medal_count'] < medalavg_awayhome_df['away_medal_count']]


# games where avg home medals < avg away medals 
df1 = medalavg_awayhome_df[medalavg_awayhome_df['home_avgmedal'] < medalavg_awayhome_df['away_avgmedal']]
print(df1)
print(df1.count())

          Games  home_team_cnt  away_team_cnt  home_medal_count  \
1   1900 Summer            122             77               225   
3   1906 Summer             21             33               102   
4   1908 Summer             34             39               368   
8   1924 Winter              3             25                10   
20  1956 Winter              3             53                 8   
25  1968 Summer              1            111                 9   
28  1972 Winter              3             60                 3   
30  1976 Winter              3             67                 7   
34  1984 Winter              3             77                 1   
36  1988 Winter              4             94                 6   

    away_medal_count  home_avgmedal  away_avgmedal  
1                368       1.844262       4.779221  
3                356       4.857143      10.787879  
4                463      10.823529      11.871795  
8                120       3.333333       4.800000  
20               142       2.666667       2.679245  
25              1048       9.000000       9.441441  
28               196       1.000000       3.266667  
30               204       2.333333       3.044776  
34               221       0.333333       2.870130  
36               257       1.500000       2.734043  
Games               10
home_team_cnt       10
away_team_cnt       10
home_medal_count    10
away_medal_count    10
home_avgmedal       10
away_avgmedal       10
dtype: int64


medalavg_awayhome_df


# games where avg home medals > avg away medals 
df2 = medalavg_awayhome_df[medalavg_awayhome_df['home_avgmedal'] > medalavg_awayhome_df['away_avgmedal']]
print(df2)
print(df2.count())

          Games  home_team_cnt  away_team_cnt  home_medal_count  \
0   1896 Summer              5             13                48   
2   1904 Summer             59             27               394   
5   1912 Summer             17             84               190   
6   1920 Summer              9             63               188   
7   1924 Summer              5             85               110   
9   1928 Summer              3             64                57   
10  1928 Winter              3             38                12   
11  1932 Summer              4             55               189   
12  1932 Winter              3             26                34   
13  1936 Summer              5            100               224   
14  1936 Winter              3             51                 7   
15  1948 Summer              5            106                61   
16  1948 Winter              3             43                28   
17  1952 Summer              5            128                40   
18  1952 Winter              3             49                19   
19  1956 Summer              6            118                72   
21  1960 Summer              5            181                88   
22  1960 Winter              4             36                27   
23  1964 Summer              5            163                62   
24  1964 Winter              3             63                17   
26  1968 Winter              3             67                 9   
27  1972 Summer              8            131               253   
29  1976 Summer              1             91                23   
31  1980 Summer              1             79               442   
32  1980 Winter              3             55                30   
33  1984 Summer              1            139               352   
35  1988 Summer              3            174                77   
37  1992 Summer              1            211                69   
38  1992 Winter              4            107                12   
39  1994 Winter              1            100                30   
40  1996 Summer              4            242               259   
41  1998 Winter              3            103                13   
42  2000 Summer              4            239               183   
43  2002 Winter              3            111                84   
44  2004 Summer              3            257                31   
45  2006 Winter              3            110                25   
46  2008 Summer              5            287               184   
47  2010 Winter              3            113                90   
48  2012 Summer              3            242               126   
49  2014 Winter              4            115                68   
50  2016 Summer              3            245                50   

    away_medal_count  home_avgmedal  away_avgmedal  
0                 95       9.600000       7.307692  
2                 92       6.677966       3.407407  
5                751      11.176471       8.940476  
6               1120      20.888889      17.777778  
7                722      22.000000       8.494118  
9                677      19.000000      10.578125  
10                77       4.000000       2.026316  
11               458      47.250000       8.327273  
12                58      11.333333       2.230769  
13               693      44.800000       6.930000  
14               101       2.333333       1.980392  
15               791      12.200000       7.462264  
16               107       9.333333       2.488372  
17               857       8.000000       6.695312  
18               117       6.333333       2.387755  
19               821      12.000000       6.957627  
21               823      17.600000       4.546961  
22               120       6.750000       3.333333  
23               967      12.400000       5.932515  
24               169       5.666667       2.682540  
26               190       3.000000       2.835821  
27               962      31.625000       7.343511  
29              1297      23.000000      14.252747  
31               942     442.000000      11.924051  
32               188      10.000000       3.418182  
33              1124     352.000000       8.086331  
35              1505      25.666667       8.649425  
37              1643      69.000000       7.786730  
38               306       3.000000       2.859813  
39               301      30.000000       3.010000  
40              1583      64.750000       6.541322  
41               427       4.333333       4.145631  
42              1821      45.750000       7.619247  
43               394      28.000000       3.549550  
44              1970      10.333333       7.665370  
45               501       8.333333       4.554545  
46              1864      36.800000       6.494774  
47               430      30.000000       3.805310  
48              1815      42.000000       7.500000  
49               529      17.000000       4.600000  
50              1973      16.666667       8.053061  
Games               41
home_team_cnt       41
away_team_cnt       41
home_medal_count    41
away_medal_count    41
home_avgmedal       41
away_avgmedal       41
dtype: int64


# using correlation 
medalavg_awayhome_df[['home_team_cnt','away_team_cnt','home_medal_count','away_medal_count','home_avgmedal','away_avgmedal']].corr(method='pearson')


# number of games where home teams' gold > away teams' gold 
print((goldcnt_awayhome_df[goldcnt_awayhome_df['home_avggoldmedal'] > goldcnt_awayhome_df['away_avggoldmedal']]).count())

Games                36
home_team_cnt        36
away_team_cnt        36
home_gold_count      36
away_gold_count      36
home_avggoldmedal    36
away_avggoldmedal    36
dtype: int64


# number of games where home teams' gold < away teams' gold 
print((goldcnt_awayhome_df[goldcnt_awayhome_df['home_avggoldmedal'] < goldcnt_awayhome_df['away_avggoldmedal']]).count())

Games                15
home_team_cnt        15
away_team_cnt        15
home_gold_count      15
away_gold_count      15
home_avggoldmedal    15
away_avggoldmedal    15
dtype: int64


# correlation 
athlete_medals_df[['avg_age','avg_height','avg_weight','total_medal']].corr(method='pearson')


# using p-values 
x = athlete_medals_df[['avg_age','avg_height','avg_weight']]
y = athlete_medals_df['total_medal']

# with sklearn
regr = LinearRegression()
regr.fit(x, y)

print('Intercept: \n', regr.intercept_)
print('Coefficients: \n', regr.coef_)

# with statsmodels
x = sm.add_constant(x) # adding a constant
 
model = sm.OLS(y, x).fit()
predictions = model.predict(x) 
 
print_model = model.summary()
print(print_model)

Intercept: 
 -0.29632281634169494
Coefficients: 
 [0.00537322 0.00213988 0.00105983]
                            OLS Regression Results                            
==============================================================================
Dep. Variable:            total_medal   R-squared:                       0.004
Model:                            OLS   Adj. R-squared:                  0.004
Method:                 Least Squares   F-statistic:                     178.2
Date:                Fri, 16 Sep 2022   Prob (F-statistic):          2.72e-115
Time:                        16:06:44   Log-Likelihood:            -1.4780e+05
No. Observations:              135571   AIC:                         2.956e+05
Df Residuals:                  135567   BIC:                         2.957e+05
Df Model:                           3                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|t|      [0.025      0.975]
------------------------------------------------------------------------------
const         -0.2963      0.048     -6.114      0.000      -0.391      -0.201
avg_age        0.0054      0.000     15.242      0.000       0.005       0.006
avg_height     0.0021      0.000      6.347      0.000       0.001       0.003
avg_weight     0.0011      0.000      4.349      0.000       0.001       0.002
==============================================================================
Omnibus:                   137690.654   Durbin-Watson:                   0.009
Prob(Omnibus):                  0.000   Jarque-Bera (JB):         15800883.137
Skew:                           4.824   Prob(JB):                         0.00
Kurtosis:                      55.001   Cond. No.                     4.77e+03
==============================================================================

Notes:
[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.
[2] The condition number is large, 4.77e+03. This might indicate that there are
strong multicollinearity or other numerical problems.


ath_df1 = (pysqldf(""" SELECT ID, Name, Games, Age, Height, Weight, COUNT(medal) AS medal_cnt
                    FROM olympics_games_distinct
                    GROUP BY ID, Name, Games, Age, Height, Weight; """))


# correlation between age, height, and weight and number of medals instead of using average values 
ath_df1[['Age', 'Height', 'Weight','medal_cnt']].corr(method='pearson')


ath_df2 = (pysqldf(""" SELECT ID, Name, Games, Age, Height, Weight,Medal, COUNT(medal) AS medal_cnt
                    FROM olympics_games_distinct
                    WHERE Medal = 'Gold'
                    GROUP BY ID, Name, Games, Age, Height, Weight, Medal; """))


# correlation between age, height, and weight and number of gold medals 
ath_df2[['Age', 'Height', 'Weight','medal_cnt']].corr(method='pearson')


athletes_gold_df.columns

Index(['ID', 'Name', 'Sex', 'avg_age', 'avg_height', 'avg_weight',
       'total_goldmedal'],
      dtype='object')


# correlation 
athletes_gold_df[['avg_age','avg_height','avg_weight','total_goldmedal']].corr(method='pearson')


# using p-values 
x = athletes_gold_df[['avg_age','avg_height','avg_weight']]
y = athletes_gold_df['total_goldmedal']

# with sklearn
regr = LinearRegression()
regr.fit(x, y)

print('Intercept: \n', regr.intercept_)
print('Coefficients: \n', regr.coef_)

# with statsmodels
x = sm.add_constant(x) # adding a constant
 
model = sm.OLS(y, x).fit()
predictions = model.predict(x) 
 
print_model = model.summary()
print(print_model)

Intercept: 
 1.3080802087573147
Coefficients: 
 [ 0.00437958  0.00024712 -0.00245168]
                            OLS Regression Results                            
==============================================================================
Dep. Variable:        total_goldmedal   R-squared:                       0.002
Model:                            OLS   Adj. R-squared:                  0.002
Method:                 Least Squares   F-statistic:                     8.356
Date:                Sun, 18 Sep 2022   Prob (F-statistic):           1.52e-05
Time:                        14:04:47   Log-Likelihood:                -11695.
No. Observations:               10425   AIC:                         2.340e+04
Df Residuals:                   10421   BIC:                         2.343e+04
Df Model:                           3                                         
Covariance Type:            nonrobust                                         
==============================================================================
                 coef    std err          t      P>|t|      [0.025      0.975]
------------------------------------------------------------------------------
const          1.3081      0.173      7.575      0.000       0.970       1.647
avg_age        0.0044      0.001      3.372      0.001       0.002       0.007
avg_height     0.0002      0.001      0.203      0.839      -0.002       0.003
avg_weight    -0.0025      0.001     -2.709      0.007      -0.004      -0.001
==============================================================================
Omnibus:                    12234.226   Durbin-Watson:                   0.036
Prob(Omnibus):                  0.000   Jarque-Bera (JB):          3492112.221
Skew:                           5.835   Prob(JB):                         0.00
Kurtosis:                      91.900   Cond. No.                     4.63e+03
==============================================================================

Notes:
[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.
[2] The condition number is large, 4.63e+03. This might indicate that there are
strong multicollinearity or other numerical problems.


sportgendermedal_df


# count number of sports where male atletes have more medals than female athletes 
display(pysqldf(""" SELECT *
                    FROM sportgender_gold_df
                    WHERE male_avgmedal > female_avgmedal; """))


# count number of sports where male atletes have less medals than female athletes 
display(pysqldf(""" SELECT *
                    FROM sportgendermedal_df
                    WHERE male_avgmedal < female_avgmedal; """))


sportgender_gold_df.columns

Index(['Sport', 'female_ath_count', 'male_ath_count', 'male_goldmedal_cnt',
       'female_goldmedal_cnt', 'female_avggoldmedal', 'male_avggoldmedal'],
      dtype='object')


# sports where male atletes have more medals than female athletes 
display(pysqldf(""" SELECT *
                    FROM sportgender_gold_df
                    WHERE male_avggoldmedal > female_avggoldmedal; """))


# sports where male atletes have less medals than female athletes 
display(pysqldf(""" SELECT *
                    FROM sportgender_gold_df
                    WHERE male_avggoldmedal < female_avggoldmedal; """))


# number of male and female athletes in each event in 2016 olympics 
display(pysqldf(""" 
                    SELECT *
                    FROM(
                        SELECT Event, 
                               COUNT(CASE WHEN Sex = 'F' THEN 1 END) AS female_cnt, 
                               COUNT(CASE WHEN Sex = 'M' THEN 1 END) AS male_cnt

                        FROM(
                            SELECT DISTINCT ID, Name, Sex, Event, Sport, Medal
                            FROM olympics_games_distinct
                            WHERE Games = '2016 Summer') 
                        GROUP BY Event 
                    )
                    WHERE (female_cnt > 0) AND (male_cnt > 0)
                """))


top_ath_df = pysqldf("""SELECT Region, NOC, ID, Name, COUNT(Medal)  AS medals 
                        FROM olympics_games_distinct
                        GROUP BY Region, NOC, ID, Name
                         """)
top_ath_df['Region'] = top_ath_df['Region'].astype(pd.StringDtype())
top_ath_df['Name'] = top_ath_df['Name'].astype(pd.StringDtype())
top_ath_df


# Rank of athletes per region based on total number of medals 
df = pysqldf(""" SELECT Region, NOC, Name, medals,  
                        DENSE_RANK() OVER(PARTITION BY Region ORDER BY medals DESC) AS regional_rank 
                    FROM top_ath_df
                    --WHERE Region = 'Zimbabwe'           
                    """)
df


# Rank of top athletes within their region 
ath_regionrank_df = pysqldf("""
                                SELECT Region, Name, medals, regional_rank 
                                FROM df
                                WHERE medals > 0 AND regional_rank = 1
                                ORDER BY medals DESC
                            """)
ath_regionrank_df


top_ath_df1 = pysqldf("""SELECT Games, ID, Name, COUNT(Medal)  AS medals 
                        FROM olympics_games_distinct
                        GROUP BY Games, ID, Name
                         """)
top_ath_df1['Games'] = top_ath_df1['Games'].astype(pd.StringDtype())
top_ath_df1['Name'] = top_ath_df1['Name'].astype(pd.StringDtype())
top_ath_df1


# Rank of athletes per olympic game based on total number of medals 
df1 = pysqldf(""" SELECT Games, Name, medals,  
                        DENSE_RANK() OVER(PARTITION BY Games ORDER BY medals DESC) AS game_rank 
                    FROM top_ath_df1
                    --WHERE Region = 'Zimbabwe'           
                    """)
df1


# Top athletes in each game
ath_gamerank_df = pysqldf("""
                                SELECT Games, Name, medals, game_rank 
                                FROM df1
                                WHERE medals > 0 AND game_rank = 1
                                ORDER BY medals DESC
                            """)
ath_gamerank_df


medalavg_awayhome_df


# total number of medals for each team type 
n_games = len(medalavg_awayhome_df['Games'])
home_medal = medalavg_awayhome_df['home_medal_count']
away_medal = medalavg_awayhome_df['away_medal_count']
homeaway_comb = np.append(home_medal,away_medal)

df = pd.DataFrame(dict(game=medalavg_awayhome_df['Games'].tolist()*2, medal=homeaway_comb.tolist(),
                       team=["Home"]*n_games + ["Away"]*n_games))

fig = px.scatter(df, x="medal", y="game", color="team",
                 title="Total medals per game",
                 labels={"medal":"Total medals"} # customize axis label
                )

fig.show()


# avg number of medals for each team type 
n_games = len(medalavg_awayhome_df['Games'])
home_medal = medalavg_awayhome_df['home_avgmedal']
away_medal = medalavg_awayhome_df['away_avgmedal']
homeaway_comb = np.append(home_medal,away_medal)

df = pd.DataFrame(dict(game=medalavg_awayhome_df['Games'].tolist()*2, medal=homeaway_comb.tolist(),
                       team=["Home"]*n_games + ["Away"]*n_games))

fig = px.scatter(df, x="medal", y="game", color="team",
                 title="Average team medals per game",
                 labels={"medal":"Average medals"} # customize axis label
                )

fig.show()


# avg number of medals for each team type 

fig = go.Figure()
fig.add_trace(go.Bar(
    y=medalavg_awayhome_df['Games'].tolist(),
    x=medalavg_awayhome_df['home_avgmedal'].tolist(),
    name='Home teams',
    orientation='h',
    marker=dict(
        color='cornflowerblue',
        line=dict(color='cornflowerblue', width=1)
    )
))
fig.add_trace(go.Bar(
    y=medalavg_awayhome_df['Games'].tolist(),
    x=medalavg_awayhome_df['away_avgmedal'].tolist(),
    name='Away teams',
    orientation='h',
    marker=dict(
        color='#EF553B',
        line=dict(color='#EF553B', width=1)
    )
))

fig.update_layout(barmode='stack', title="Average team medals per game")
fig.show()


# avg number of gold medals for each team type 

fig = go.Figure()
fig.add_trace(go.Bar(
    y=goldcnt_awayhome_df['Games'].tolist(),
    x=goldcnt_awayhome_df['home_avggoldmedal'].tolist(),
    name='Home teams',
    orientation='h',
    marker=dict(
        color='cornflowerblue',
        line=dict(color='cornflowerblue', width=1)
    )
))
fig.add_trace(go.Bar(
    y=goldcnt_awayhome_df['Games'].tolist(),
    x=goldcnt_awayhome_df['away_avggoldmedal'].tolist(),
    name='Away teams',
    orientation='h',
    marker=dict(
        color='#EF553B',
        line=dict(color='#EF553B', width=1)
    )
))

fig.update_layout(barmode='stack', title="Average team gold medals per game")
fig.show()


athlete_medals_df


import plotly.express as px
fig = px.scatter_matrix(athlete_medals_df,
    dimensions=["avg_age", "avg_height", "avg_weight", "total_medal"],
    color="Sex",opacity=0.5)
fig.show()


for col in athlete_medals_df[["avg_age", "avg_height", "avg_weight"]]: 
    fig = px.scatter(athlete_medals_df, x=athlete_medals_df[col], y="total_medal",
                 size="total_medal", color="Sex",
                 hover_name="Name", trendline="ols")
    #fig.update_layeout()
    fig.show()


fig = px.scatter_matrix(athletes_gold_df,
    dimensions=["avg_age", "avg_height", "avg_weight", "total_goldmedal"],
    color="Sex",opacity=0.5)
fig.show()


sportgendermedal_df


# avg number of medals for each gender in each sport 

fig = go.Figure()
fig.add_trace(go.Bar(
    y=sportgendermedal_df['Sport'].tolist(),
    x=sportgendermedal_df['male_avgmedal'].tolist(),
    name='Male',
    orientation='h',
    marker=dict(
        color='cornflowerblue',
        line=dict(color='cornflowerblue', width=1)
    )
))
fig.add_trace(go.Bar(
    y=sportgendermedal_df['Sport'].tolist(),
    x=sportgendermedal_df['female_avgmedal'].tolist(),
    name='Female',
    orientation='h',
    marker=dict(
        color='#EF553B',
        line=dict(color='#EF553B', width=1)
    )
))

fig.update_layout(barmode='stack', title="Average medals per sport")
fig.show()


# avg number of medals for each gender in each sport 
n_sport = len(sportgendermedal_df['Sport'])
f_avg= sportgendermedal_df['female_avgmedal']
m_avg = sportgendermedal_df['male_avgmedal']
fm_comb = np.append(m_avg,f_avg)

df = pd.DataFrame(dict(sport=sportgendermedal_df['Sport'].tolist()*2, medal=fm_comb.tolist(),
                       Gender= ["Male"]*n_sport + ["Female"]*n_sport))

fig = px.scatter(df, x="medal", y="sport", color="Gender",
                 title="Average gender medals per sport",
                 labels={"medal":"Average medals"} # customize axis label
                )

fig.show()


sportgender_gold_df


# avg number of gold medals for each gender in each sport 
fig = go.Figure()
fig.add_trace(go.Bar(
    y=sportgender_gold_df['Sport'].tolist(),
    x=sportgender_gold_df['male_avggoldmedal'].tolist(),
    name='Male',
    orientation='h',
    marker=dict(
        color='cornflowerblue',
        line=dict(color='cornflowerblue', width=1)
    )
))
fig.add_trace(go.Bar(
    y=sportgender_gold_df['Sport'].tolist(),
    x=sportgender_gold_df['female_avggoldmedal'].tolist(),
    name='Female',
    orientation='h',
    marker=dict(
        color='#EF553B',
        line=dict(color='#EF553B', width=1)
    )
))

fig.update_layout(barmode='stack', title="Average gold medals per sport")
fig.show()

	ID	Name	Sex	Age	Height	Weight	Team	NOC	Region	Games	Year	Season	Sport	Event	City	Country	Medal	Notes
0	1	A Dijiang	M	24	180	80	China	CHN	China	1992 Summer	1992	Summer	Basketball	Basketball Men's Basketball	Barcelona	Spain	<NA>	<NA>
1	2	A Lamusi	M	23	170	60	China	CHN	China	2012 Summer	2012	Summer	Judo	Judo Men's Extra-Lightweight	London	United Kingdom	<NA>	<NA>
2	3	Gunnar Nielsen Aaby	M	24	<NA>	<NA>	Denmark	DEN	Denmark	1920 Summer	1920	Summer	Football	Football Men's Football	Antwerpen	Belgium	<NA>	<NA>
3	4	Edgar Lindenau Aabye	M	34	<NA>	<NA>	Denmark/Sweden	DEN	Denmark	1900 Summer	1900	Summer	Tug-Of-War	Tug-Of-War Men's Tug-Of-War	Paris	France	Gold	<NA>
4	5	Christine Jacoba Aaftink	F	21	185	82	Netherlands	NED	Netherlands	1988 Winter	1988	Winter	Speed Skating	Speed Skating Women's 500 metres	Calgary	Canada	<NA>	<NA>
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
271111	135569	Andrzej ya	M	29	179	89	Poland-1	POL	Poland	1976 Winter	1976	Winter	Luge	Luge Mixed (Men)'s Doubles	Innsbruck	Austria	<NA>	<NA>
271112	135570	Piotr ya	M	27	176	59	Poland	POL	Poland	2014 Winter	2014	Winter	Ski Jumping	Ski Jumping Men's Large Hill, Individual	Sochi	Russia	<NA>	<NA>
271113	135570	Piotr ya	M	27	176	59	Poland	POL	Poland	2014 Winter	2014	Winter	Ski Jumping	Ski Jumping Men's Large Hill, Team	Sochi	Russia	<NA>	<NA>
271114	135571	Tomasz Ireneusz ya	M	30	185	96	Poland	POL	Poland	1998 Winter	1998	Winter	Bobsleigh	Bobsleigh Men's Four	Nagano	Japan	<NA>	<NA>
271115	135571	Tomasz Ireneusz ya	M	34	185	96	Poland	POL	Poland	2002 Winter	2002	Winter	Bobsleigh	Bobsleigh Men's Four	Salt Lake City	United States	<NA>	<NA>

	ID	Name	Sex	Age	Height	Weight	Team	NOC	Region	Games	Year	Season	Sport	Event	City	Country	Medal	Notes
0	1	A Dijiang	M	24.0	180.0	80.0	China	CHN	China	1992 Summer	1992	Summer	Basketball	Basketball Men's Basketball	Barcelona	Spain	None	None
1	2	A Lamusi	M	23.0	170.0	60.0	China	CHN	China	2012 Summer	2012	Summer	Judo	Judo Men's Extra-Lightweight	London	United Kingdom	None	None
2	3	Gunnar Nielsen Aaby	M	24.0	NaN	NaN	Denmark	DEN	Denmark	1920 Summer	1920	Summer	Football	Football Men's Football	Antwerpen	Belgium	None	None
3	4	Edgar Lindenau Aabye	M	34.0	NaN	NaN	Denmark/Sweden	DEN	Denmark	1900 Summer	1900	Summer	Tug-Of-War	Tug-Of-War Men's Tug-Of-War	Paris	France	Gold	None
4	5	Christine Jacoba Aaftink	F	21.0	185.0	82.0	Netherlands	NED	Netherlands	1988 Winter	1988	Winter	Speed Skating	Speed Skating Women's 500 metres	Calgary	Canada	None	None
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
269726	135569	Andrzej ya	M	29.0	179.0	89.0	Poland-1	POL	Poland	1976 Winter	1976	Winter	Luge	Luge Mixed (Men)'s Doubles	Innsbruck	Austria	None	None
269727	135570	Piotr ya	M	27.0	176.0	59.0	Poland	POL	Poland	2014 Winter	2014	Winter	Ski Jumping	Ski Jumping Men's Large Hill, Individual	Sochi	Russia	None	None
269728	135570	Piotr ya	M	27.0	176.0	59.0	Poland	POL	Poland	2014 Winter	2014	Winter	Ski Jumping	Ski Jumping Men's Large Hill, Team	Sochi	Russia	None	None
269729	135571	Tomasz Ireneusz ya	M	30.0	185.0	96.0	Poland	POL	Poland	1998 Winter	1998	Winter	Bobsleigh	Bobsleigh Men's Four	Nagano	Japan	None	None
269730	135571	Tomasz Ireneusz ya	M	34.0	185.0	96.0	Poland	POL	Poland	2002 Winter	2002	Winter	Bobsleigh	Bobsleigh Men's Four	Salt Lake City	United States	None	None

	ID	Name	Sex	Age	Height	Weight	Team	NOC	Region	Games	Year	Season	Sport	Event	City	Country	Medal	Notes
0	704	Dsir Antoine Acket	M	27.0	NaN	NaN	Belgium	BEL	Belgium	1932 Summer	1932	Summer	Art Competitions	Art Competitions Mixed Painting, Unknown Event	Los Angeles	United States	None	None
1	2449	William Truman Aldrich	M	48.0	NaN	NaN	United States	USA	United States	1928 Summer	1928	Summer	Art Competitions	Art Competitions Mixed Painting, Drawings And Water Colors	Amsterdam	Netherlands	None	None
2	2777	Hermann Reinhard Alker	M	43.0	NaN	NaN	Germany	GER	Germany	1928 Summer	1928	Summer	Art Competitions	Art Competitions Mixed Architecture, Architectural Designs	Amsterdam	Netherlands	None	None
3	2777	Hermann Reinhard Alker	M	43.0	NaN	NaN	Germany	GER	Germany	1928 Summer	1928	Summer	Art Competitions	Art Competitions Mixed Architecture, Designs For Town Planning	Amsterdam	Netherlands	None	None
4	2777	Hermann Reinhard Alker	M	51.0	NaN	NaN	Germany	GER	Germany	1936 Summer	1936	Summer	Art Competitions	Art Competitions Mixed Architecture, Unknown Event	Berlin	Germany	None	None
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
607	134046	ngel Zrraga Argelles	M	41.0	NaN	NaN	Mexico	MEX	Mexico	1928 Summer	1928	Summer	Art Competitions	Art Competitions Mixed Painting, Paintings	Amsterdam	Netherlands	None	None
608	135072	Anna Katrina Zinkeisen (-Heseltine)	F	46.0	NaN	NaN	Great Britain	GBR	United Kingdom	1948 Summer	1948	Summer	Art Competitions	Art Competitions Mixed Painting, Paintings	London	United Kingdom	None	None
609	135072	Anna Katrina Zinkeisen (-Heseltine)	F	46.0	NaN	NaN	Great Britain	GBR	United Kingdom	1948 Summer	1948	Summer	Art Competitions	Art Competitions Mixed Painting, Unknown Event	London	United Kingdom	None	None
610	135073	Doris Clare Zinkeisen (-Johnstone)	F	49.0	NaN	NaN	Great Britain	GBR	United Kingdom	1948 Summer	1948	Summer	Art Competitions	Art Competitions Mixed Painting, Unknown Event	London	United Kingdom	None	None
611	135173	Henri Achille Zo	M	58.0	NaN	NaN	France	FRA	France	1932 Summer	1932	Summer	Art Competitions	Art Competitions Mixed Painting, Unknown Event	Los Angeles	United States	None	None

	ID	Name	Sex	Age	Height	Weight	Team	NOC	Region	Games	Year	Season	Sport	Event	City	Country	Medal	Notes
0	3515	Paulo Amotun Lokoro	M	24.0	170.0	61.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Athletics	Athletics Men's 1,500 metres	Rio de Janeiro	Brazil	None	Refugee Olympic Team
1	4379	Rami Anis	M	25.0	178.0	78.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Swimming	Swimming Men's 100 metres Freestyle	Rio de Janeiro	Brazil	None	Refugee Olympic Team
2	4379	Rami Anis	M	25.0	178.0	78.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Swimming	Swimming Men's 100 metres Butterfly	Rio de Janeiro	Brazil	None	Refugee Olympic Team
3	11364	Yiech Pur Biel	M	21.0	178.0	62.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Athletics	Athletics Men's 800 metres	Rio de Janeiro	Brazil	None	Refugee Olympic Team
4	16287	Mabika Yolande Bukasa	F	28.0	170.0	70.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Judo	Judo Women's Middleweight	Rio de Janeiro	Brazil	None	Refugee Olympic Team
5	20693	James Nyang Chiengjiek	M	24.0	179.0	59.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Athletics	Athletics Men's 400 metres	Rio de Janeiro	Brazil	None	Refugee Olympic Team
6	31292	Fritz Eccard	M	NaN	NaN	NaN	Unknown	UNK	None	1912 Summer	1912	Summer	Art Competitions	Art Competitions Mixed Architecture	Stockholm	Sweden	None	Unknown
7	80325	Popole Misenga	M	24.0	180.0	90.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Judo	Judo Men's Middleweight	Rio de Janeiro	Brazil	None	Refugee Olympic Team
8	60444	Yonas Kinde	M	36.0	172.0	57.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Athletics	Athletics Men's Marathon	Rio de Janeiro	Brazil	None	Refugee Olympic Team
9	65813	A. Laffen	M	NaN	NaN	NaN	Unknown	UNK	None	1912 Summer	1912	Summer	Art Competitions	Art Competitions Mixed Architecture	Stockholm	Sweden	None	Unknown
10	71067	Anjelina Nadai Lohalith	F	23.0	163.0	50.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Athletics	Athletics Women's 1,500 metres	Rio de Janeiro	Brazil	None	Refugee Olympic Team
11	71113	Rose Nathike Lokonyen	F	21.0	157.0	50.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Athletics	Athletics Women's 800 metres	Rio de Janeiro	Brazil	None	Refugee Olympic Team
12	74902	Yusra Mardini	F	18.0	157.0	53.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Swimming	Swimming Women's 100 metres Freestyle	Rio de Janeiro	Brazil	None	Refugee Olympic Team
13	74902	Yusra Mardini	F	18.0	157.0	53.0	Refugee Olympic Athletes	ROT	None	2016 Summer	2016	Summer	Swimming	Swimming Women's 100 metres Butterfly	Rio de Janeiro	Brazil	None	Refugee Olympic Team

	Name	id_count
0	Zhang Li	5
1	Wolfgang Mller	5
2	Wang Nan	5
3	Lszl Szab	5
4	Li Jie	5
...	...	...
134727	Th Anh	1
134728	Luis ngel Fernando de los Santos Grossi	1
134729	Jean Hauptmanns	1
134730	Eleonora Margarida Josephina Scmitt	1
134731	Gabrielle Marie "Gabby" Adcock (White-)	1

Milestone 2: Descriptive Statistics¶

Import libraries¶

Load data¶

Descriptive Statistics¶

Using SQL¶

Count¶

Home vs. Away teams medals¶

Mean, Median, Mode, Min, Max, IQR¶

Number of distinct sport each athlete play¶

Check if there are any athlete labeled as both male and female¶

Region with athletes who play different sports¶

Percent difference¶

Hypothesis 1 tables¶

Metric 1: Total number of medals¶

Number of home and away teams in every game¶

Metric 2: Total number of gold medals¶

Hypothesis 2 tables¶

Metric 1: Total number of medals¶

Metric 2: Total number of gold medals¶

Hypothesis 3 tables¶

Metric 1: Total medals¶

Metric 2: Total gold medals¶

Using Pandas¶

Descriptive Stats¶

Prove/disprove hypothesis 1¶

Metric 1: Total medals¶

Metric 2: Total gold medals¶

Prove/disprove hypothesis 2¶

Metric 1: Total medals¶

Metric 2: Total gold medals¶

Prove/disprove hypothesis 3¶

Metric 1: Total medals¶

Metric 2: Total gold medals¶

Other questions¶

Do male athletes acquire more gold medals than female in mixed sport events ?¶

Extra - Using SQL window functions¶

Visualizations¶

Hypothesis 1: host teams perform better than visiting teams¶

Hypothesis 2: age, height, and weight influence performance¶

Hypothesis 3: Male perform better than female in some sports (vice versa)¶

	Sex	Medal	total_medal
0	F	Bronze	3771
1	F	Gold	3747
2	F	Silver	3735
3	M	Bronze	9524
4	M	Gold	9622
5	M	Silver	9373

	Sport	Sex	gender_count
0	Aeronautics	M	1
1	Alpine Skiing	F	996
2	Alpine Skiing	M	1739
3	Alpinism	F	1
4	Alpinism	M	24
...	...	...	...
111	Water Polo	M	2262
112	Weightlifting	F	356
113	Weightlifting	M	2526
114	Wrestling	F	222
115	Wrestling	M	4766

	Games	TeamType	medal_count
0	1896 Summer	away	95
1	1896 Summer	home	48
2	1900 Summer	away	368
3	1900 Summer	home	225
4	1904 Summer	away	92
...	...	...	...
97	2012 Summer	home	126
98	2014 Winter	away	529
99	2014 Winter	home	68
100	2016 Summer	away	1973
101	2016 Summer	home	50

	Team
0	Greece
1	Greece-3
2	Greece-1
3	Greece-2
4	Ethnikos Gymnastikos Syllogos

	Games	Team	Region	Country
0	1904 Summer	Vesper Boat Club	United States	United States
1	1904 Summer	United States	United States	United States
2	1904 Summer	New York Athletic Club #2-4	United States	United States
3	1904 Summer	Mound City Rowing Club-2	United States	United States
4	1904 Summer	La Salle Turnverein, Chicago	United States	United States
5	1904 Summer	Christian Brothers' College-1	United States	United States
6	1904 Summer	Passaic Turnverein, Passaic	United States	United States
7	1904 Summer	Chicago Athletic Association-2	United States	United States
8	1904 Summer	United States-2	United States	United States
9	1904 Summer	Davenport Turngemeinde, Davenport	United States	United States
10	1904 Summer	Norwegier Turnverein, Brooklyn	United States	United States
11	1904 Summer	Turnverein Vorwrts, Cleveland	United States	United States
12	1904 Summer	New York Turnverein, New York	United States	United States
13	1904 Summer	United States-10	United States	United States
14	1904 Summer	St. Rose-2	United States	United States
15	1904 Summer	New York Athletic Club-1	United States	United States
16	1904 Summer	St. Louis Southwest Turnverein #1-2	United States	United States
17	1904 Summer	Chicago Archers	United States	United States
18	1904 Summer	Boston Archers	United States	United States
19	1904 Summer	Western Rowing Club-3	United States	United States
20	1904 Summer	United States Golf Association-3	United States	United States
21	1904 Summer	Trans-Mississippi Golf Association-2	United States	United States
22	1904 Summer	New York Athletic Club-4	United States	United States
23	1904 Summer	United States-9	United States	United States
24	1904 Summer	Cincinnati Archers	United States	United States
25	1904 Summer	United States-6	United States	United States
26	1904 Summer	Western Golf Association-1	United States	United States
27	1904 Summer	New York Athletic Club #1-1	United States	United States
28	1904 Summer	United States-7	United States	United States
29	1904 Summer	Concordia Turnverein, St Louis	United States	United States
30	1904 Summer	Century Boat Club-1	United States	United States
31	1904 Summer	St. Louis Amateur Athletic Association	United States	United States
32	1904 Summer	United States-14	United States	United States
33	1904 Summer	Central Turnverein, Chicago	United States	United States
34	1904 Summer	Turnverein Vorwrts, Chicago	United States	United States
35	1904 Summer	Missouri Athletic Club-3	United States	United States
36	1904 Summer	Seawanhaka Boat Club-1	United States	United States
37	1904 Summer	Milwaukee Athletic Club-1	United States	United States
38	1904 Summer	St. Louis Southwest Turnverein #2-3	United States	United States
39	1904 Summer	Socialer Turnverein, Detroit	United States	United States
40	1904 Summer	Potomac Archers	United States	United States
41	1904 Summer	Ravenswood Boat Club-2	United States	United States
42	1904 Summer	United States-11	United States	United States
43	1904 Summer	Atalanta Boat Club-1	United States	United States
44	1904 Summer	Atalanta Boat Club-2	United States	United States
45	1904 Summer	New York Athletic Club	United States	United States
46	1904 Summer	South St Louis Turnverein, St Louis	United States	United States
47	1904 Summer	Milwaukee Turnverein, Milwaukee	United States	United States
48	1904 Summer	Independent Rowing Club-3	United States	United States
49	1904 Summer	Philadelphia Turngemeinde, Philadelphia	United States	United States
50	1904 Summer	United States-13	United States	United States
51	1904 Summer	United States-12	United States	United States
52	1904 Summer	United States-4	United States	United States
53	1904 Summer	Germany/United States	United States	United States
54	1904 Summer	Chicago Athletic Association	United States	United States
55	1904 Summer	United States-5	United States	United States
56	1904 Summer	United States-8	United States	United States
57	1904 Summer	United States-3	United States	United States
58	1904 Summer	United States-1	United States	United States

	ID	Name	num_of_observations
0	106296	Heikki Ilmari Savolainen	39
1	115354	Joseph "Josy" Stoffel	38
2	119591	Ioannis Theofilakis	36
3	89187	Takashi Ono	32
4	119590	Alexandros Theofilakis	32
...	...	...	...
135566	135562	Milan Zyka	1
135567	135564	Yevgeny Aleksandrovich Zykov	1
135568	135566	James Francis "Jim" Zylker	1
135569	135568	Olga Igorevna Zyuzkova	1
135570	135569	Andrzej ya	1

	ID	Name	Sex	Age	Height	Weight	Team	NOC	Region	Games	Year	Season	Sport	Event	City	Country	Medal	Notes
0	106296	Heikki Ilmari Savolainen	M	20.0	172.0	64.0	Finland	FIN	Finland	1928 Summer	1928	Summer	Gymnastics	Gymnastics Men's Individual All-Around	Amsterdam	Netherlands	None	None
1	106296	Heikki Ilmari Savolainen	M	20.0	172.0	64.0	Finland	FIN	Finland	1928 Summer	1928	Summer	Gymnastics	Gymnastics Men's Team All-Around	Amsterdam	Netherlands	None	None
2	106296	Heikki Ilmari Savolainen	M	20.0	172.0	64.0	Finland	FIN	Finland	1928 Summer	1928	Summer	Gymnastics	Gymnastics Men's Horse Vault	Amsterdam	Netherlands	None	None
3	106296	Heikki Ilmari Savolainen	M	20.0	172.0	64.0	Finland	FIN	Finland	1928 Summer	1928	Summer	Gymnastics	Gymnastics Men's Parallel Bars	Amsterdam	Netherlands	None	None
4	106296	Heikki Ilmari Savolainen	M	20.0	172.0	64.0	Finland	FIN	Finland	1928 Summer	1928	Summer	Gymnastics	Gymnastics Men's Horizontal Bar	Amsterdam	Netherlands	None	None
5	106296	Heikki Ilmari Savolainen	M	20.0	172.0	64.0	Finland	FIN	Finland	1928 Summer	1928	Summer	Gymnastics	Gymnastics Men's Rings	Amsterdam	Netherlands	None	None
6	106296	Heikki Ilmari Savolainen	M	20.0	172.0	64.0	Finland	FIN	Finland	1928 Summer	1928	Summer	Gymnastics	Gymnastics Men's Pommelled Horse	Amsterdam	Netherlands	Bronze	None
7	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Individual All-Around	Los Angeles	United States	Bronze	None
8	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Team All-Around	Los Angeles	United States	Bronze	None
9	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Floor Exercise	Los Angeles	United States	None	None
10	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Horse Vault	Los Angeles	United States	None	None
11	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Parallel Bars	Los Angeles	United States	Bronze	None
12	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Horizontal Bar	Los Angeles	United States	Silver	None
13	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Rings	Los Angeles	United States	None	None
14	106296	Heikki Ilmari Savolainen	M	24.0	172.0	64.0	Finland	FIN	Finland	1932 Summer	1932	Summer	Gymnastics	Gymnastics Men's Pommelled Horse	Los Angeles	United States	None	None
15	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Individual All-Around	Berlin	Germany	None	None
16	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Team All-Around	Berlin	Germany	Bronze	None
17	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Floor Exercise	Berlin	Germany	None	None
18	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Horse Vault	Berlin	Germany	None	None
19	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Parallel Bars	Berlin	Germany	None	None
20	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Horizontal Bar	Berlin	Germany	None	None
21	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Rings	Berlin	Germany	None	None
22	106296	Heikki Ilmari Savolainen	M	28.0	172.0	64.0	Finland	FIN	Finland	1936 Summer	1936	Summer	Gymnastics	Gymnastics Men's Pommelled Horse	Berlin	Germany	None	None
23	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Individual All-Around	London	United Kingdom	None	None
24	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Team All-Around	London	United Kingdom	Gold	None
25	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Floor Exercise	London	United Kingdom	None	None
26	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Horse Vault	London	United Kingdom	None	None
27	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Parallel Bars	London	United Kingdom	None	None
28	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Horizontal Bar	London	United Kingdom	None	None
29	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Rings	London	United Kingdom	None	None
30	106296	Heikki Ilmari Savolainen	M	40.0	172.0	64.0	Finland	FIN	Finland	1948 Summer	1948	Summer	Gymnastics	Gymnastics Men's Pommelled Horse	London	United Kingdom	Gold	None
31	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Individual All-Around	Helsinki	Finland	None	None
32	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Team All-Around	Helsinki	Finland	Bronze	None
33	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Floor Exercise	Helsinki	Finland	None	None
34	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Horse Vault	Helsinki	Finland	None	None
35	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Parallel Bars	Helsinki	Finland	None	None
36	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Horizontal Bar	Helsinki	Finland	None	None
37	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Rings	Helsinki	Finland	None	None
38	106296	Heikki Ilmari Savolainen	M	44.0	172.0	64.0	Finland	FIN	Finland	1952 Summer	1952	Summer	Gymnastics	Gymnastics Men's Pommelled Horse	Helsinki	Finland	None	None

	ID	Name	num_games	num_sports_played
0	79855	Ian Millar	10	1
1	65378	Afanasijs Kuzmins	9	1
2	99155	Hubert Raudaschl	9	1
3	14388	Francisco Boza Dibos	8	1
4	26880	Rajmond Debevec	8	1
...	...	...	...	...
135566	135564	Yevgeny Aleksandrovich Zykov	1	1
135567	135566	James Francis "Jim" Zylker	1	1
135568	135568	Olga Igorevna Zyuzkova	1	1
135569	135569	Andrzej ya	1	1
135570	135570	Piotr ya	1	1

	ID	Name	num_sports	num_games
0	6386	Karl Johan Baadsvik	4	1
1	24534	Bronisaw Czech	4	3
2	32330	Launceston Elliot	4	2
3	52387	William Archibald "Bill" Irwin	4	1
4	54456	Alexander Viggo Jensen	4	2
...	...	...	...	...
135566	135567	Aleksandr Viktorovich Zyuzin	1	2
135567	135568	Olga Igorevna Zyuzkova	1	1
135568	135569	Andrzej ya	1	1
135569	135570	Piotr ya	1	1
135570	135571	Tomasz Ireneusz ya	1	2

	ID	Name	Games	Sport
0	6386	Karl Johan Baadsvik	1936 Winter	Ski Jumping
1	6386	Karl Johan Baadsvik	1936 Winter	Cross Country Skiing
2	6386	Karl Johan Baadsvik	1936 Winter	Nordic Combined
3	6386	Karl Johan Baadsvik	1936 Winter	Alpine Skiing

	Sex	total_medal
0	F	11253
1	M	28519

	Sex	total_medal
0	F	11253
1	M	28519

	Sport	male_count	female_count
0	Aeronautics	1	0
1	Alpine Skiing	1739	996
2	Alpinism	24	1
3	Archery	613	500
4	Art Competitions	1610	204
5	Athletics	15542	6529
6	Badminton	399	412
7	Baseball	761	0
8	Basketball	2481	932
9	Basque Pelota	2	0
10	Beach Volleyball	194	189
11	Biathlon	764	371
12	Bobsleigh	1585	109
13	Boxing	5197	65
14	Canoeing	2504	702
15	Cricket	24	0
16	Croquet	7	3
17	Cross Country Skiing	1683	717
18	Curling	186	160
19	Cycling	5105	714
20	Diving	831	635
21	Equestrianism	1886	459
22	Fencing	3243	880
23	Figure Skating	748	824
24	Football	5427	734
25	Freestyle Skiing	359	267
26	Golf	148	70
27	Gymnastics	2635	1499
28	Handball	1675	1027
29	Hockey	2829	996
30	Ice Hockey	3386	498
31	Jeu De Paume	11	0
32	Judo	1967	757
33	Lacrosse	60	0
34	Luge	544	228
35	Military Ski Patrol	24	0
36	Modern Pentathlon	750	114
37	Motorboating	13	1
38	Nordic Combined	605	0
39	Polo	87	0

	Value
Stat
COUNT	210917.0
MEAN	175.0
MEDIAN	171.0
MIN	127.0
MAX	226.0

	ID	Name	num_sports
0	82613	Jaqueline Mouro	3
1	110716	Georgia Simmerling	3
2	118525	Sheila Christine Taormina	3
3	968	Margarete "Grete" Adler	2
4	3615	Ana Paula Rodrigues Connelly (-Rodrigues Henkel)	2
...	...	...	...
33976	135550	Vera Vasilyevna Zyatikova	1
33977	135553	Galina Ivanovna Zybina (-Fyodorova)	1
33978	135560	Stavroula Zygouri	1
33979	135563	Olesya Nikolayevna Zykina	1
33980	135568	Olga Igorevna Zyuzkova	1

	Games	home_team_cnt	away_team_cnt	home_medal_count	away_medal_count	home_avgmedal	away_avgmedal
0	1896 Summer	5	13	48	95	9.600000	7.307692
1	1900 Summer	122	77	225	368	1.844262	4.779221
2	1904 Summer	59	27	394	92	6.677966	3.407407
3	1906 Summer	21	33	102	356	4.857143	10.787879
4	1908 Summer	34	39	368	463	10.823529	11.871795
5	1912 Summer	17	84	190	751	11.176471	8.940476
6	1920 Summer	9	63	188	1120	20.888889	17.777778
7	1924 Summer	5	85	110	722	22.000000	8.494118
8	1924 Winter	3	25	10	120	3.333333	4.800000
9	1928 Summer	3	64	57	677	19.000000	10.578125
10	1928 Winter	3	38	12	77	4.000000	2.026316
11	1932 Summer	4	55	189	458	47.250000	8.327273
12	1932 Winter	3	26	34	58	11.333333	2.230769
13	1936 Summer	5	100	224	693	44.800000	6.930000
14	1936 Winter	3	51	7	101	2.333333	1.980392
15	1948 Summer	5	106	61	791	12.200000	7.462264
16	1948 Winter	3	43	28	107	9.333333	2.488372
17	1952 Summer	5	128	40	857	8.000000	6.695312
18	1952 Winter	3	49	19	117	6.333333	2.387755
19	1956 Summer	6	118	72	821	12.000000	6.957627
20	1956 Winter	3	53	8	142	2.666667	2.679245
21	1960 Summer	5	181	88	823	17.600000	4.546961
22	1960 Winter	4	36	27	120	6.750000	3.333333
23	1964 Summer	5	163	62	967	12.400000	5.932515
24	1964 Winter	3	63	17	169	5.666667	2.682540
25	1968 Summer	1	111	9	1048	9.000000	9.441441
26	1968 Winter	3	67	9	190	3.000000	2.835821
27	1972 Summer	8	131	253	962	31.625000	7.343511
28	1972 Winter	3	60	3	196	1.000000	3.266667
29	1976 Summer	1	91	23	1297	23.000000	14.252747
30	1976 Winter	3	67	7	204	2.333333	3.044776
31	1980 Summer	1	79	442	942	442.000000	11.924051
32	1980 Winter	3	55	30	188	10.000000	3.418182
33	1984 Summer	1	139	352	1124	352.000000	8.086331
34	1984 Winter	3	77	1	221	0.333333	2.870130
35	1988 Summer	3	174	77	1505	25.666667	8.649425
36	1988 Winter	4	94	6	257	1.500000	2.734043
37	1992 Summer	1	211	69	1643	69.000000	7.786730
38	1992 Winter	4	107	12	306	3.000000	2.859813
39	1994 Winter	1	100	30	301	30.000000	3.010000
40	1996 Summer	4	242	259	1583	64.750000	6.541322
41	1998 Winter	3	103	13	427	4.333333	4.145631
42	2000 Summer	4	239	183	1821	45.750000	7.619247
43	2002 Winter	3	111	84	394	28.000000	3.549550
44	2004 Summer	3	257	31	1970	10.333333	7.665370
45	2006 Winter	3	110	25	501	8.333333	4.554545
46	2008 Summer	5	287	184	1864	36.800000	6.494774
47	2010 Winter	3	113	90	430	30.000000	3.805310
48	2012 Summer	3	242	126	1815	42.000000	7.500000
49	2014 Winter	4	115	68	529	17.000000	4.600000
50	2016 Summer	3	245	50	1973	16.666667	8.053061

	Games	home_team_cnt	away_team_cnt	away_gold_count	home_gold_count
0	1896 Summer	5	13	52	10.0
1	1900 Summer	122	77	148	50.0
2	1904 Summer	59	27	45	128.0
3	1906 Summer	21	33	133	24.0
4	1908 Summer	34	39	147	147.0
5	1912 Summer	17	84	223	103.0
6	1920 Summer	9	63	436	57.0
7	1924 Summer	5	85	240	37.0
8	1924 Winter	3	25	55	NaN
9	1928 Summer	3	64	225	20.0
10	1928 Winter	3	38	30	NaN
11	1932 Summer	4	55	148	81.0
12	1932 Winter	3	26	22	10.0
13	1936 Summer	5	100	219	93.0
14	1936 Winter	3	51	32	4.0
15	1948 Summer	5	106	282	7.0
16	1948 Winter	3	43	37	4.0
17	1952 Summer	5	128	298	8.0
18	1952 Winter	3	49	38	7.0
19	1956 Summer	6	118	269	33.0
20	1956 Winter	3	53	49	2.0
21	1960 Summer	5	181	273	36.0
22	1960 Winter	4	36	31	19.0
23	1964 Summer	5	163	315	32.0
24	1964 Winter	3	63	56	5.0
25	1968 Summer	1	111	356	3.0
26	1968 Winter	3	67	62	4.0
27	1972 Summer	8	131	330	74.0
28	1972 Winter	3	60	69	1.0
29	1976 Summer	1	91	438	NaN
30	1976 Winter	3	67	68	2.0
31	1980 Summer	1	79	270	187.0
32	1980 Winter	3	55	48	24.0
33	1984 Summer	1	139	311	186.0
34	1984 Winter	3	77	74	NaN
35	1988 Summer	3	174	492	28.0
36	1988 Winter	4	94	87	NaN
37	1992 Summer	1	211	511	48.0
38	1992 Winter	4	107	99	5.0
39	1994 Winter	1	100	102	8.0
40	1996 Summer	4	242	449	159.0
41	1998 Winter	3	103	137	8.0
42	2000 Summer	4	239	603	60.0
43	2002 Winter	3	111	151	11.0
44	2004 Summer	3	257	656	8.0
45	2006 Winter	3	110	165	11.0
46	2008 Summer	5	287	597	74.0
47	2010 Winter	3	113	107	67.0
48	2012 Summer	3	242	584	48.0
49	2014 Winter	4	115	169	33.0
50	2016 Summer	3	245	629	36.0

	Games	home_team_cnt	away_team_cnt	home_gold_count	away_gold_count	home_avggoldmedal	away_avggoldmedal
0	1896 Summer	5	13	10.0	52	2.000000	4.000000
1	1900 Summer	122	77	50.0	148	0.409836	1.922078
2	1904 Summer	59	27	128.0	45	2.169492	1.666667
3	1906 Summer	21	33	24.0	133	1.142857	4.030303
4	1908 Summer	34	39	147.0	147	4.323529	3.769231
5	1912 Summer	17	84	103.0	223	6.058824	2.654762
6	1920 Summer	9	63	57.0	436	6.333333	6.920635
7	1924 Summer	5	85	37.0	240	7.400000	2.823529
8	1924 Winter	3	25	0.0	55	0.000000	2.200000
9	1928 Summer	3	64	20.0	225	6.666667	3.515625
10	1928 Winter	3	38	0.0	30	0.000000	0.789474
11	1932 Summer	4	55	81.0	148	20.250000	2.690909
12	1932 Winter	3	26	10.0	22	3.333333	0.846154
13	1936 Summer	5	100	93.0	219	18.600000	2.190000
14	1936 Winter	3	51	4.0	32	1.333333	0.627451
15	1948 Summer	5	106	7.0	282	1.400000	2.660377
16	1948 Winter	3	43	4.0	37	1.333333	0.860465
17	1952 Summer	5	128	8.0	298	1.600000	2.328125
18	1952 Winter	3	49	7.0	38	2.333333	0.775510
19	1956 Summer	6	118	33.0	269	5.500000	2.279661
20	1956 Winter	3	53	2.0	49	0.666667	0.924528
21	1960 Summer	5	181	36.0	273	7.200000	1.508287
22	1960 Winter	4	36	19.0	31	4.750000	0.861111
23	1964 Summer	5	163	32.0	315	6.400000	1.932515
24	1964 Winter	3	63	5.0	56	1.666667	0.888889
25	1968 Summer	1	111	3.0	356	3.000000	3.207207
26	1968 Winter	3	67	4.0	62	1.333333	0.925373
27	1972 Summer	8	131	74.0	330	9.250000	2.519084
28	1972 Winter	3	60	1.0	69	0.333333	1.150000
29	1976 Summer	1	91	0.0	438	0.000000	4.813187
30	1976 Winter	3	67	2.0	68	0.666667	1.014925
31	1980 Summer	1	79	187.0	270	187.000000	3.417722
32	1980 Winter	3	55	24.0	48	8.000000	0.872727
33	1984 Summer	1	139	186.0	311	186.000000	2.237410
34	1984 Winter	3	77	0.0	74	0.000000	0.961039
35	1988 Summer	3	174	28.0	492	9.333333	2.827586
36	1988 Winter	4	94	0.0	87	0.000000	0.925532
37	1992 Summer	1	211	48.0	511	48.000000	2.421801
38	1992 Winter	4	107	5.0	99	1.250000	0.925234
39	1994 Winter	1	100	8.0	102	8.000000	1.020000
40	1996 Summer	4	242	159.0	449	39.750000	1.855372
41	1998 Winter	3	103	8.0	137	2.666667	1.330097
42	2000 Summer	4	239	60.0	603	15.000000	2.523013
43	2002 Winter	3	111	11.0	151	3.666667	1.360360
44	2004 Summer	3	257	8.0	656	2.666667	2.552529
45	2006 Winter	3	110	11.0	165	3.666667	1.500000
46	2008 Summer	5	287	74.0	597	14.800000	2.080139
47	2010 Winter	3	113	67.0	107	22.333333	0.946903
48	2012 Summer	3	242	48.0	584	16.000000	2.413223
49	2014 Winter	4	115	33.0	169	8.250000	1.469565
50	2016 Summer	3	245	36.0	629	12.000000	2.567347

	ID	Name	Sex	avg_age	avg_height	avg_weight	total_medal
0	94406	Michael Fred Phelps, II	M	24.333333	193.0	91.0	28
1	67046	Larysa Semenivna Latynina (Diriy-)	F	24.600000	161.0	52.0	18
2	4198	Nikolay Yefimovich Andrianov	M	23.000000	166.0	60.0	15
3	11951	Ole Einar Bjrndalen	M	30.857143	178.0	65.0	13
4	74420	Edoardo Mangiarotti	M	33.400000	NaN	NaN	13
...	...	...	...	...	...	...	...
135566	135567	Aleksandr Viktorovich Zyuzin	M	26.000000	183.0	72.0	0
135567	135568	Olga Igorevna Zyuzkova	F	33.000000	171.0	69.0	0
135568	135569	Andrzej ya	M	29.000000	179.0	89.0	0
135569	135570	Piotr ya	M	27.000000	176.0	59.0	0
135570	135571	Tomasz Ireneusz ya	M	32.000000	185.0	96.0	0

	Sport	male_cnt	female_cnt
0	Aeronautics	1	0
1	Alpine Skiing	5431	3398
2	Alpinism	24	1
3	Archery	1319	1015
4	Art Competitions	2030	233
5	Athletics	26958	11666
6	Badminton	717	740
7	Baseball	894	0
8	Basketball	3280	1256
9	Basque Pelota	2	0
10	Beach Volleyball	288	276
11	Biathlon	3030	1863
12	Bobsleigh	2915	143
13	Boxing	5975	72
14	Canoeing	4791	1380
15	Cricket	24	0
16	Croquet	13	6
17	Cross Country Skiing	5748	3385
18	Curling	241	222
19	Cycling	9433	1394
20	Diving	1632	1210
21	Equestrianism	5097	1246
22	Fencing	8735	2000
23	Figure Skating	1126	1172
24	Football	5733	1012
25	Freestyle Skiing	504	433
26	Golf	177	70
27	Gymnastics	17578	9129
28	Handball	2264	1401
29	Hockey	3958	1459
30	Ice Hockey	4762	754
31	Jeu De Paume	11	0
32	Judo	2708	1093
33	Lacrosse	60	0
34	Luge	1102	377
35	Military Ski Patrol	24	0
36	Modern Pentathlon	1513	164
37	Motorboating	16	1
38	Nordic Combined	1344	0
39	Polo	95	0
40	Racquets	12	0
41	Rhythmic Gymnastics	0	658
42	Roque	4	0
43	Rowing	8402	2193
44	Rugby	162	0
45	Rugby Sevens	151	148
46	Sailing	5623	926
47	Shooting	9724	1724
48	Short Track Speed Skating	773	761
49	Skeleton	133	66
50	Ski Jumping	2371	30
51	Snowboarding	520	416
52	Softball	0	478
53	Speed Skating	3532	2081
54	Swimming	13345	9850
55	Synchronized Swimming	0	909
56	Table Tennis	1002	953
57	Taekwondo	307	299
58	Tennis	1684	1178
59	Trampolining	76	76
60	Triathlon	266	263
61	Tug-Of-War	170	0
62	Volleyball	1861	1543
63	Water Polo	3358	488
64	Weightlifting	3474	463
65	Wrestling	6850	304

	ID	Name	Sex	Age	Height	Weight	Team	NOC	Region	Games	Year	Season	Sport	Event	City	Country	Medal	Notes
0	25866	Jos de Amzola y Aspiza	M	26	None	None	Spain	ESP	Spain	1900 Summer	1900	Summer	Basque Pelota	Basque Pelota Men's Two-Man Teams With Cesta	Paris	France	Gold	None
1	126675	Francisco Villota y Baquiola	M	26	None	None	Spain	ESP	Spain	1900 Summer	1900	Summer	Basque Pelota	Basque Pelota Men's Two-Man Teams With Cesta	Paris	France	Gold	None

	Sport	female_ath_count	male_ath_count	female_medal_cnt	male_medal_cnt	female_avgmedal	male_avgmedal
0	Alpine Skiing	996	1739	213	215	0.213855	0.123634
1	Alpinism	1	24	1	24	1.000000	1.000000
2	Archery	500	613	121	232	0.242000	0.378467
3	Art Competitions	204	1610	11	145	0.053922	0.090062
4	Athletics	6529	15542	1275	2694	0.195283	0.173337
5	Badminton	412	399	84	84	0.203883	0.210526
6	Basketball	932	2481	393	687	0.421674	0.276904
7	Beach Volleyball	189	194	36	36	0.190476	0.185567
8	Biathlon	371	764	150	258	0.404313	0.337696
9	Bobsleigh	109	1585	24	374	0.220183	0.235962
10	Boxing	65	5197	24	920	0.369231	0.177025
11	Canoeing	702	2504	282	883	0.401709	0.352636
12	Croquet	3	7	0	8	0.000000	1.142857
13	Cross Country Skiing	717	1683	339	437	0.472803	0.259655
14	Curling	160	186	68	84	0.425000	0.451613
15	Cycling	714	5105	176	1087	0.246499	0.212929
16	Diving	635	831	201	226	0.316535	0.271961
17	Equestrianism	459	1886	207	758	0.450980	0.401909
18	Fencing	880	3243	349	1394	0.396591	0.429849
19	Figure Skating	824	748	191	195	0.231796	0.260695
20	Football	734	5427	302	1269	0.411444	0.233831
21	Freestyle Skiing	267	359	51	51	0.191011	0.142061
22	Golf	70	148	6	40	0.085714	0.270270
23	Gymnastics	1499	2635	701	1555	0.467645	0.590133
24	Handball	1027	1675	472	588	0.459591	0.351045
25	Hockey	996	2829	478	1050	0.479920	0.371156
26	Ice Hockey	498	3386	300	1230	0.602410	0.363260
27	Judo	757	1967	196	351	0.258917	0.178444
28	Luge	228	544	45	135	0.197368	0.248162
29	Modern Pentathlon	114	750	15	171	0.131579	0.228000
30	Motorboating	1	13	0	7	0.000000	0.538462
31	Rowing	1483	6204	720	2225	0.485502	0.358640
32	Rugby Sevens	148	151	36	38	0.243243	0.251656
33	Sailing	629	3851	134	1087	0.213037	0.282264
34	Shooting	737	4145	146	1082	0.198100	0.261037
35	Short Track Speed Skating	209	235	140	144	0.669856	0.612766
36	Skeleton	45	101	12	18	0.266667	0.178218
37	Ski Jumping	30	844	3	204	0.100000	0.241706
38	Snowboarding	239	328	45	45	0.188285	0.137195
39	Speed Skating	528	1054	242	338	0.458333	0.320683
40	Swimming	3621	5144	1374	1674	0.379453	0.325428
41	Table Tennis	377	372	84	84	0.222812	0.225806
42	Taekwondo	229	241	72	72	0.314410	0.298755
43	Tennis	486	760	140	200	0.288066	0.263158
44	Trampolining	44	49	15	15	0.340909	0.306122
45	Triathlon	175	180	15	15	0.085714	0.083333
46	Volleyball	1129	1374	474	495	0.419841	0.360262
47	Water Polo	337	2262	191	866	0.566766	0.382847
48	Weightlifting	356	2526	105	541	0.294944	0.214173
49	Wrestling	222	4766	68	1228	0.306306	0.257658

	Age	Height	Weight
count	260416.000000	210917.000000	207146.000000
mean	25.454776	175.338953	70.680872
std	6.163869	10.518507	14.330655
min	10.000000	127.000000	25.000000
25%	21.000000	168.000000	60.000000
50%	24.000000	175.000000	70.000000
75%	28.000000	183.000000	79.000000
max	97.000000	226.000000	214.000000

	Age	Height	Weight
count	73976.000000	67378.000000	66585.000000
mean	23.697713	167.839740	60.011414
std	5.695049	8.778528	10.195580
min	11.000000	127.000000	25.000000
25%	20.000000	162.000000	54.000000
50%	23.000000	168.000000	59.000000
75%	27.000000	173.000000	65.000000
max	74.000000	213.000000	167.000000

	Age	Height	Weight
count	186440.000000	143539.000000	140561.000000
mean	26.151947	178.859125	75.735090
std	6.203904	9.360033	13.188625
min	10.000000	127.000000	28.000000
25%	22.000000	172.000000	67.000000
50%	25.000000	179.000000	74.000000
75%	29.000000	185.000000	83.000000
max	97.000000	226.000000	214.000000

	ID	avg_age	avg_height	avg_weight	total_medal
count	101590.000000	101590.000000	101590.000000	101590.000000	101590.000000
mean	67174.582420	25.818763	178.512115	75.072944	0.280726
std	39156.392093	5.658437	8.061680	11.576350	0.689189
min	1.000000	10.000000	127.000000	28.000000	0.000000
25%	33104.250000	22.000000	176.000000	71.000000	0.000000
50%	66973.000000	25.000000	176.315410	71.941320	0.000000
75%	101153.750000	28.000000	183.000000	80.000000	0.000000
max	135571.000000	97.000000	226.000000	214.000000	28.000000

	ID	avg_age	avg_height	avg_weight	total_medal
count	33981.000000	33981.000000	33981.000000	33981.000000	33981.000000
mean	69613.901239	23.902592	169.748115	62.578981	0.331156
std	39018.934491	5.200672	8.354558	10.336990	0.808477
min	5.000000	11.000000	127.000000	25.000000	0.000000
25%	36379.000000	20.000000	164.000000	55.000000	0.000000
50%	69604.000000	23.666667	170.000000	62.000000	0.000000
75%	103309.000000	27.000000	176.315410	71.000000	0.000000
max	135568.000000	74.000000	213.000000	167.000000	18.000000

	home_team_cnt	away_team_cnt	home_medal_count	away_medal_count	home_avgmedal	away_avgmedal
home_team_cnt	1.000000	-0.170452	0.399454	-0.145728	-0.113990	0.012361
away_team_cnt	-0.170452	1.000000	0.113671	0.894231	0.125224	0.178024
home_medal_count	0.399454	0.113671	1.000000	0.266063	0.610863	0.447170
away_medal_count	-0.145728	0.894231	0.266063	1.000000	0.245816	0.559944
home_avgmedal	-0.113990	0.125224	0.610863	0.245816	1.000000	0.306029
away_avgmedal	0.012361	0.178024	0.447170	0.559944	0.306029	1.000000

	avg_age	avg_height	avg_weight	total_medal
avg_age	1.000000	0.076059	0.141231	0.046408
avg_height	0.076059	1.000000	0.762834	0.043841
avg_weight	0.141231	0.762834	1.000000	0.044606
total_medal	0.046408	0.043841	0.044606	1.000000

	Age	Height	Weight	medal_cnt
Age	1.000000	0.082661	0.163917	0.010306
Height	0.082661	1.000000	0.774509	0.041712
Weight	0.163917	0.774509	1.000000	0.037541
medal_cnt	0.010306	0.041712	0.037541	1.000000

	Age	Height	Weight	medal_cnt
Age	1.000000	0.094635	0.167282	-0.049534
Height	0.094635	1.000000	0.804981	-0.045838
Weight	0.167282	0.804981	1.000000	-0.083971
medal_cnt	-0.049534	-0.045838	-0.083971	1.000000

	avg_age	avg_height	avg_weight	total_goldmedal
avg_age	1.000000	0.075681	0.132929	0.027825
avg_height	0.075681	1.000000	0.785820	-0.028184
avg_weight	0.132929	0.785820	1.000000	-0.036213
total_goldmedal	0.027825	-0.028184	-0.036213	1.000000

	Sport	female_ath_count	male_ath_count	male_goldmedal_cnt	female_goldmedal_cnt	female_avggoldmedal	male_avggoldmedal
0	Archery	500	613	92	43.0	0.086000	0.150082
1	Art Competitions	204	1610	48	1.0	0.004902	0.029814
2	Badminton	412	399	27	27.0	0.065534	0.067669
3	Bobsleigh	109	1585	125	8.0	0.073394	0.078864
4	Croquet	3	7	4	0.0	0.000000	0.571429
5	Curling	160	186	28	22.0	0.137500	0.150538
6	Equestrianism	459	1886	269	55.0	0.119826	0.142630
7	Fencing	880	3243	475	119.0	0.135227	0.146469
8	Figure Skating	824	748	66	65.0	0.078883	0.088235
9	Golf	70	148	13	2.0	0.028571	0.087838
10	Gymnastics	1499	2635	557	234.0	0.156104	0.211385
11	Luge	228	544	47	15.0	0.065789	0.086397
12	Modern Pentathlon	114	750	57	5.0	0.043860	0.076000
13	Motorboating	1	13	7	0.0	0.000000	0.538462
14	Rugby Sevens	148	151	13	12.0	0.081081	0.086093
15	Sailing	629	3851	400	47.0	0.074722	0.103869
16	Shooting	737	4145	361	49.0	0.066486	0.087093
17	Ski Jumping	30	844	68	1.0	0.033333	0.080569
18	Table Tennis	377	372	27	27.0	0.071618	0.072581
19	Wrestling	222	4766	395	18.0	0.081081	0.082879

	Event	female_cnt	male_cnt
0	Badminton Mixed Doubles	16	16
1	Equestrianism Mixed Dressage, Individual	39	21
2	Equestrianism Mixed Dressage, Team	25	19
3	Equestrianism Mixed Jumping, Individual	13	62
4	Equestrianism Mixed Jumping, Team	11	49
5	Equestrianism Mixed Three-Day Event, Individual	23	42
6	Equestrianism Mixed Three-Day Event, Team	20	31
7	Sailing Mixed Multihull	20	20
8	Tennis Mixed Doubles	15	15